Added Derby dialect. Finished HSQL dialect. Documentation.

* Improved DEFAULT value specifications.
* Fixed bug in buildObjects where the ResultSet could be closed by the
automatic create table attempt.
* DbInspector now uses the dialect's reported DATETIME class preference.
* Improved IciqlException SQLState code checks.
* Integrated LIMIT and OFFSET expression appending in dialects.
* Updated to H2 1.3.159
* Allow reopening of a memory database in the test suite.

22 files changed, 383 insertions, 146 deletions

diff --git a/tests/com/iciql/test/AliasMapTest.java b/tests/com/iciql/test/AliasMapTest.java
index 0f91365..092f38b 100644
--- a/tests/com/iciql/test/AliasMapTest.java
+++ b/tests/com/iciql/test/AliasMapTest.java
@@ -42,7 +42,7 @@ public class AliasMapTest {
 	 */
 	@Test
 	public void testObjectAliasMapping() throws Exception {
-		Db db = IciqlSuite.openDb();
+		Db db = IciqlSuite.openNewDb();
 		db.insertAll(Product.getList());
 
 		// baseline count is the next id value
@@ -91,7 +91,7 @@ public class AliasMapTest {
 	 */
 	@Test
 	public void testPrimitiveAliasMapping() throws Exception {
-		Db db = IciqlSuite.openDb();
+		Db db = IciqlSuite.openNewDb();
 		PrimitivesModel model = new PrimitivesModel();
 		model.myLong = 100L;
 		db.insert(model);
diff --git a/tests/com/iciql/test/AnnotationsTest.java b/tests/com/iciql/test/AnnotationsTest.java
index b156cac..7e4e918 100644
--- a/tests/com/iciql/test/AnnotationsTest.java
+++ b/tests/com/iciql/test/AnnotationsTest.java
@@ -53,7 +53,7 @@ public class AnnotationsTest {
 
 	@Before
 	public void setUp() {
-		db = IciqlSuite.openDb();
+		db = IciqlSuite.openNewDb();
 		db.insertAll(Product.getList());
 		db.insertAll(ProductAnnotationOnly.getList());
 		db.insertAll(ProductMixedAnnotation.getList());
@@ -69,7 +69,15 @@ public class AnnotationsTest {
 		// test indexes are created, and columns are in the right order
 		DatabaseMetaData meta = db.getConnection().getMetaData();
 		boolean isH2 = meta.getDatabaseProductName().equals("H2");
-		ResultSet rs = meta.getIndexInfo(null, "PUBLIC", "ANNOTATEDPRODUCT", false, true);
+		boolean isDerby = meta.getDatabaseProductName().equals("Apache Derby");
+		ResultSet rs;
+		if (isDerby) {
+			// Derby defaults to USERNAME schema
+			rs = meta.getIndexInfo(null, "SA", "ANNOTATEDPRODUCT", false, true);
+		} else {
+			// H2, HSQL default to PUBLIC schema
+			rs = meta.getIndexInfo(null, "PUBLIC", "ANNOTATEDPRODUCT", false, true);
+		}
 		// first index is primary key index
 		// H2 gives this a testable name.
 		assertTrue(rs.next());
@@ -104,7 +112,7 @@ public class AnnotationsTest {
 		} catch (IciqlException e) {
 			assertEquals(IciqlException.CODE_UNMAPPED_FIELD, e.getIciqlCode());
 		}
-		
+
 		// 10 objects, 10 autoIncremented unique values
 		assertEquals(10, db.from(p).selectDistinct(p.productName).size());
 
diff --git a/tests/com/iciql/test/BooleanModelTest.java b/tests/com/iciql/test/BooleanModelTest.java
index 6111e72..1e1630a 100644
--- a/tests/com/iciql/test/BooleanModelTest.java
+++ b/tests/com/iciql/test/BooleanModelTest.java
@@ -39,7 +39,7 @@ public class BooleanModelTest {
 

 	@Test

 	public void testBooleanColumn() {

-		Db db = IciqlSuite.openDb();

+		Db db = IciqlSuite.openNewDb();

 		db.insertAll(BooleanModel.getList());

 		BooleanAsIntModel b = new BooleanAsIntModel();

 		List<BooleanAsIntModel> models = db.from(b).select();

@@ -82,7 +82,7 @@ public class BooleanModelTest {
 

 	@Test

 	public void testIntColumn() {

-		Db db = IciqlSuite.openDb();

+		Db db = IciqlSuite.openNewDb();

 		// insert INT column

 		db.insertAll(BooleanAsIntModel.getList());

 

diff --git a/tests/com/iciql/test/ClobTest.java b/tests/com/iciql/test/ClobTest.java
index bd85716..3b32dcb 100644
--- a/tests/com/iciql/test/ClobTest.java
+++ b/tests/com/iciql/test/ClobTest.java
@@ -38,13 +38,13 @@ public class ClobTest {
 	@Test
 	public void testClob() throws Exception {
 		String create = "CREATE TABLE CLOB_TEST(ID INT PRIMARY KEY, WORDS {0})";
-		Db db = IciqlSuite.openDb();
+		Db db = IciqlSuite.openNewDb();
 		db.executeUpdate(MessageFormat.format(create, "VARCHAR(255)"));
 		db.insertAll(StringRecord.getList());
 		testSimpleUpdate(db, "VARCHAR fail");
 		db.close();
 
-		db = IciqlSuite.openDb();		
+		db = IciqlSuite.openNewDb();
 		db.executeUpdate(MessageFormat.format(create, "CLOB"));
 		db.insertAll(StringRecord.getList());
 		testSimpleUpdate(db, "CLOB fail because of single quote artifacts");
@@ -95,7 +95,8 @@ public class ClobTest {
 		}
 
 		public static List<StringRecord> getList() {
-			StringRecord[] list = { create(1, "Once upon a midnight dreary, while I pondered weak and weary,"),
+			StringRecord[] list = {
+					create(1, "Once upon a midnight dreary, while I pondered weak and weary,"),
 					create(2, "Over many a quaint and curious volume of forgotten lore,"),
 					create(3, "While I nodded, nearly napping, suddenly there came a tapping,"),
 					create(4, "As of some one gently rapping, rapping at my chamber door."),
diff --git a/tests/com/iciql/test/ConcurrencyTest.java b/tests/com/iciql/test/ConcurrencyTest.java
index 1641eb1..e248265 100644
--- a/tests/com/iciql/test/ConcurrencyTest.java
+++ b/tests/com/iciql/test/ConcurrencyTest.java
@@ -22,8 +22,8 @@ import static org.junit.Assert.assertTrue;
 import java.util.List;
 import java.util.concurrent.atomic.AtomicInteger;
 
-import org.junit.After;
 import org.junit.Before;
+import org.junit.Ignore;
 import org.junit.Test;
 
 import com.iciql.Db;
@@ -37,43 +37,43 @@ import com.iciql.util.Utils;
  */
 public class ConcurrencyTest {
 
-	private Db db;
-	private int numberOfTests = 200;
+	private int numberOfTests = 800;
 
 	@Before
 	public void setUp() {
-		db = IciqlSuite.openDb();
+		Db db = IciqlSuite.openNewDb();
 		db.insertAll(Product.getList());
 	}
 
-	@After
-	public void tearDown() {
-		db.close();
-	}
-
 	@Test
 	public void testAliasSharing() throws Exception {
-		// Single-threaded example of why aliases can NOT be shared.
-		Product p = new Product();
-		Query<Product> query1 = db.from(p);
-		Query<Product> query2 = db.from(p);
-
-		// if you could share alias instances both counts should be equal
-		long count1 = 0;
+		Db db = IciqlSuite.openCurrentDb();
 		try {
-			count1 = query1.where(p.category).is("Beverages").selectCount();
-		} catch (IciqlException e) {
-			assertEquals(IciqlException.CODE_UNMAPPED_FIELD, e.getIciqlCode());
+			// Single-threaded example of why aliases can NOT be shared.
+			Product p = new Product();
+			Query<Product> query1 = db.from(p);
+			Query<Product> query2 = db.from(p);
+
+			// if you could share alias instances both counts should be equal
+			long count1 = 0;
+			try {
+				count1 = query1.where(p.category).is("Beverages").selectCount();
+			} catch (IciqlException e) {
+				assertEquals(IciqlException.CODE_UNMAPPED_FIELD, e.getIciqlCode());
+			}
+			long count2 = query2.where(p.category).is("Beverages").selectCount();
+
+			// but they aren't
+			assertEquals(0, count1);
+			assertEquals(2, count2);
+			assertTrue(count1 != count2);
+		} finally {
+			db.close();
 		}
-		long count2 = query2.where(p.category).is("Beverages").selectCount();
-
-		// but they aren't
-		assertEquals(0, count1);
-		assertEquals(2, count2);
-		assertTrue(count1 != count2);
 	}
 
 	@Test
+	@Ignore
 	public void testConcurrencyFinal() throws Exception {
 		// Multi-threaded example of why aliases can NOT be shared.
 		//
@@ -114,6 +114,7 @@ public class ConcurrencyTest {
 	}
 
 	@Test
+	@Ignore
 	public void testConcurrencyThreadLocal() throws Exception {
 		List<Thread> threads = Utils.newArrayList();
 		final AtomicInteger failures = new AtomicInteger(0);
@@ -149,45 +150,50 @@ public class ConcurrencyTest {
 	}
 
 	private void test(int testCase, Product p) throws AssertionError {
-		List<Product> list;
-		switch (testCase) {
-		case 0:
-			list = db.from(p).where(p.productName).is("Chai").select();
-			assertEquals(1, list.size());
-			assertEquals("Chai", list.get(0).productName);
-			break;
-		case 1:
-			list = db.from(p).where(p.category).is("Condiments").select();
-			assertEquals(5, list.size());
-			break;
-		case 3:
-			list = db.from(p).where(p.productName).is("Aniseed Syrup").select();
-			assertEquals(1, list.size());
-			assertEquals("Aniseed Syrup", list.get(0).productName);
-			break;
-		case 4:
-			list = db.from(p).where(p.productName).like("Chef%").select();
-			assertEquals(2, list.size());
-			assertTrue(list.get(0).productName.startsWith("Chef"));
-			assertTrue(list.get(1).productName.startsWith("Chef"));
-			break;
-		case 6:
-			list = db.from(p).where(p.unitsInStock).exceeds(0).select();
-			assertEquals(9, list.size());
-			break;
-		case 7:
-			list = db.from(p).where(p.unitsInStock).is(0).select();
-			assertEquals(1, list.size());
-			assertEquals("Chef Anton's Gumbo Mix", list.get(0).productName);
-			break;
-		case 9:
-			list = db.from(p).where(p.productId).is(7).select();
-			assertEquals(1, list.size());
-			assertTrue(7 == list.get(0).productId);
-			break;
-		default:
-			list = db.from(p).select();
-			assertEquals(10, list.size());
+		Db db = IciqlSuite.openCurrentDb();
+		try {
+			List<Product> list;
+			switch (testCase) {
+			case 0:
+				list = db.from(p).where(p.productName).is("Chai").select();
+				assertEquals(1, list.size());
+				assertEquals("Chai", list.get(0).productName);
+				break;
+			case 1:
+				list = db.from(p).where(p.category).is("Condiments").select();
+				assertEquals(5, list.size());
+				break;
+			case 3:
+				list = db.from(p).where(p.productName).is("Aniseed Syrup").select();
+				assertEquals(1, list.size());
+				assertEquals("Aniseed Syrup", list.get(0).productName);
+				break;
+			case 4:
+				list = db.from(p).where(p.productName).like("Chef%").select();
+				assertEquals(2, list.size());
+				assertTrue(list.get(0).productName.startsWith("Chef"));
+				assertTrue(list.get(1).productName.startsWith("Chef"));
+				break;
+			case 6:
+				list = db.from(p).where(p.unitsInStock).exceeds(0).select();
+				assertEquals(9, list.size());
+				break;
+			case 7:
+				list = db.from(p).where(p.unitsInStock).is(0).select();
+				assertEquals(1, list.size());
+				assertEquals("Chef Anton's Gumbo Mix", list.get(0).productName);
+				break;
+			case 9:
+				list = db.from(p).where(p.productId).is(7).select();
+				assertEquals(1, list.size());
+				assertTrue(7 == list.get(0).productId);
+				break;
+			default:
+				list = db.from(p).select();
+				assertEquals(10, list.size());
+			}
+		} finally {
+			db.close();
 		}
 	}
 }
diff --git a/tests/com/iciql/test/DefaultValuesTest.java b/tests/com/iciql/test/DefaultValuesTest.java
index 7c7c84f..1374379 100644
--- a/tests/com/iciql/test/DefaultValuesTest.java
+++ b/tests/com/iciql/test/DefaultValuesTest.java
@@ -34,7 +34,7 @@ public class DefaultValuesTest {
 

 	@Test

 	public void testDefaultObjectValues() {

-		Db db = IciqlSuite.openDb();

+		Db db = IciqlSuite.openNewDb();

 

 		// insert random model

 		DefaultValuesModel model = new DefaultValuesModel();

diff --git a/tests/com/iciql/test/EnumsTest.java b/tests/com/iciql/test/EnumsTest.java
index fd6af2f..d010683 100644
--- a/tests/com/iciql/test/EnumsTest.java
+++ b/tests/com/iciql/test/EnumsTest.java
@@ -40,7 +40,7 @@ public class EnumsTest {
 

 	@Before

 	public void setUp() {

-		db = IciqlSuite.openDb();

+		db = IciqlSuite.openNewDb();

 		db.insertAll(EnumIdModel.createList());

 		db.insertAll(EnumOrdinalModel.createList());

 		db.insertAll(EnumStringModel.createList());

diff --git a/tests/com/iciql/test/IciqlSuite.java b/tests/com/iciql/test/IciqlSuite.java
index 5842a2e..c43bdaf 100644
--- a/tests/com/iciql/test/IciqlSuite.java
+++ b/tests/com/iciql/test/IciqlSuite.java
@@ -15,8 +15,13 @@
  */

 package com.iciql.test;

 

+import java.io.PrintStream;

 import java.sql.SQLException;

 import java.text.MessageFormat;

+import java.util.Arrays;

+import java.util.Collections;

+import java.util.Comparator;

+import java.util.List;

 import java.util.concurrent.atomic.AtomicInteger;

 

 import org.junit.Assert;

@@ -27,7 +32,13 @@ import org.junit.runner.notification.Failure;
 import org.junit.runners.Suite;

 import org.junit.runners.Suite.SuiteClasses;

 

+import com.beust.jcommander.JCommander;

+import com.beust.jcommander.Parameter;

+import com.beust.jcommander.ParameterException;

+import com.beust.jcommander.Parameters;

+import com.iciql.Constants;

 import com.iciql.Db;

+import com.iciql.util.StringUtils;

 

 /**

  * JUnit 4 iciql test suite.

@@ -45,29 +56,62 @@ import com.iciql.Db;
 		RuntimeQueryTest.class, SamplesTest.class, UpdateTest.class, UUIDTest.class })

 public class IciqlSuite {

 

-	private static final TestDb[] TEST_DBS = { 

-			new TestDb("H2", "jdbc:h2:mem:"),

-			new TestDb("HSQL", "jdbc:hsqldb:mem:db{0,number,000}") };

+	private static final TestDb[] TEST_DBS = { new TestDb("H2", "jdbc:h2:mem:db{0,number,000}"),

+			new TestDb("HSQL", "jdbc:hsqldb:mem:db{0,number,000}"),

+			new TestDb("Derby", "jdbc:derby:memory:db{0,number,000};create=true") };

 

 	private static final TestDb DEFAULT_TEST_DB = TEST_DBS[0];

 

+	private static final PrintStream ERR = System.err;

+

 	private static AtomicInteger openCount = new AtomicInteger(0);

 

+	private static String username = "sa";

+

+	private static String password = "sa";

+

+	private static PrintStream out = System.out;

+

 	public static void assertStartsWith(String value, String startsWith) {

 		Assert.assertTrue(MessageFormat.format("Expected \"{0}\", got: \"{1}\"", startsWith, value),

 				value.startsWith(startsWith));

 	}

 

-	public static Db openDb() {

+	/**

+	 * Increment the database counter, open and create a new database.

+	 * 

+	 * @return a fresh database

+	 */

+	public static Db openNewDb() {

 		String testUrl = System.getProperty("iciql.url");

 		if (testUrl == null) {

 			testUrl = DEFAULT_TEST_DB.url;

 		}

 		testUrl = MessageFormat.format(testUrl, openCount.incrementAndGet());

-		return Db.open(testUrl, "sa", "sa");

+		return Db.open(testUrl, username, password);

 	}

 

-	public static String getDatabaseName(Db db) {

+	/**

+	 * Open the current database.

+	 * 

+	 * @return the current database

+	 */

+	public static Db openCurrentDb() {

+		String testUrl = System.getProperty("iciql.url");

+		if (testUrl == null) {

+			testUrl = DEFAULT_TEST_DB.url;

+		}

+		testUrl = MessageFormat.format(testUrl, openCount.get());

+		return Db.open(testUrl, username, password);

+	}

+

+	/**

+	 * Returns the name of the underlying database engine for the Db object.

+	 * 

+	 * @param db

+	 * @return the database engine name

+	 */

+	public static String getDatabaseEngineName(Db db) {

 		String database = "";

 		try {

 			database = db.getConnection().getMetaData().getDatabaseProductName();

@@ -76,23 +120,161 @@ public class IciqlSuite {
 		return database;

 	}

 

-	public static void main(String... args) {

+	/**

+	 * Returns true if the underlying database engine is Derby.

+	 * 

+	 * @param db

+	 * @return true if underlying database engine is Derby

+	 */

+	public static boolean isDerby(Db db) {

+		return IciqlSuite.getDatabaseEngineName(db).equals("Apache Derby");

+	}

+

+	/**

+	 * Returns true if the underlying database engine is H2.

+	 * 

+	 * @param db

+	 * @return true if underlying database engine is H2

+	 */

+	public static boolean isH2(Db db) {

+		return IciqlSuite.getDatabaseEngineName(db).equals("H2");

+	}

+

+	/**

+	 * Gets the default schema of the underlying database engine.

+	 * 

+	 * @param db

+	 * @return the default schema

+	 */

+	public static String getDefaultSchema(Db db) {

+		if (isDerby(db)) {

+			// Derby sets default schema name to username

+			return username.toUpperCase();

+		}

+		return "PUBLIC";

+	}

+

+	/**

+	 * Main entry point for the test suite. Executing this method will run the

+	 * test suite on all registered databases.

+	 * 

+	 * @param args

+	 * @throws Exception

+	 */

+	public static void main(String... args) throws Exception {

+		Params params = new Params();

+		JCommander jc = new JCommander(params);

+		try {

+			jc.parse(args);

+		} catch (ParameterException t) {

+			usage(jc, t);

+		}

+

+		// Replace System.out with a file

+		if (!StringUtils.isNullOrEmpty(params.outputFile)) {

+			out = new PrintStream(params.outputFile);

+			System.setErr(out);

+		}

+

 		SuiteClasses suiteClasses = IciqlSuite.class.getAnnotation(SuiteClasses.class);

+		long quickestDatabase = Long.MAX_VALUE;

+		String dividerMajor = buildDivider('*', 70);

+		String dividerMinor = buildDivider('-', 70);

+

+		// Header

+		out.println(dividerMajor);

+		out.println(MessageFormat.format("{0} {1} ({2}) testing {3} databases", Constants.NAME,

+				Constants.VERSION, Constants.VERSION_DATE, TEST_DBS.length));

+		out.println(dividerMajor);

+		out.println();

+

+		showProperty("java.vendor");

+		showProperty("java.runtime.version");

+		showProperty("java.vm.name");

+		showProperty("os.name");

+		showProperty("os.version");

+		showProperty("os.arch");

+		out.println();

+

+		// Test a database

 		for (TestDb testDb : TEST_DBS) {

-			System.out.println("*********************************************");

-			System.out.println("Testing " + testDb.name + " " + testDb.getVersion());

-			System.out.println("*********************************************");

+			out.println(dividerMinor);

+			out.println("Testing " + testDb.name + " " + testDb.getVersion());

+			out.println(dividerMinor);

 			System.setProperty("iciql.url", testDb.url);

 			Result result = JUnitCore.runClasses(suiteClasses.value());

-			System.out.println(MessageFormat.format("{0} runs, {1} failures, {2} ignores in {3} msecs",

+			testDb.runtime = result.getRunTime();

+			if (testDb.runtime < quickestDatabase) {

+				quickestDatabase = testDb.runtime;

+			}

+			out.println(MessageFormat.format("{0} tests: {1} failures, {2} ignores in {3,number,0.000} secs",

 					result.getRunCount(), result.getFailureCount(), result.getIgnoreCount(),

-					result.getRunTime()));

-			for (Failure failure : result.getFailures()) {

-				System.out.println(MessageFormat.format("{0}: {1}", failure.getTestHeader(),

-						failure.getMessage()));

+					result.getRunTime() / 1000f));

+			if (result.getFailureCount() == 0) {

+				out.println();

+			} else {

+				for (Failure failure : result.getFailures()) {

+					out.println(MessageFormat.format("\n  + {0}\n    {1}\n", failure.getTestHeader(),

+							failure.getMessage()));

+				}

 			}

+		}

+

+		// Display runtime results sorted by performance leader

+		out.println(dividerMajor);

+		out.println(MessageFormat.format("{0} {1} ({2}) test suite performance results", Constants.NAME,

+				Constants.VERSION, Constants.VERSION_DATE));

+		out.println(dividerMajor);

+		List<TestDb> dbs = Arrays.asList(TEST_DBS);

+		Collections.sort(dbs, new Comparator<TestDb>() {

+

+			@Override

+			public int compare(TestDb o1, TestDb o2) {

+				if (o1.runtime == o2.runtime) {

+					return 0;

+				}

+				if (o1.runtime > o2.runtime) {

+					return 1;

+				}

+				return -1;

+			}

+		});

+		for (TestDb testDb : dbs) {

+			out.println(MessageFormat.format("{0} {1} {2,number,0.000} secs  ({3,number,#.0}x)",

+					StringUtils.pad(testDb.name, 6, " ", true),

+					StringUtils.pad(testDb.getVersion(), 22, " ", true), testDb.runtime / 1000f,

+					((double) testDb.runtime) / quickestDatabase));

+		}

+

+		// close PrintStream and restore System.err

+		out.close();

+		System.setErr(ERR);

+	}

+

+	private static void showProperty(String name) {

+		out.print(StringUtils.pad(name, 25, " ", true));

+		out.println(System.getProperty(name));

+	}

+

+	private static void usage(JCommander jc, ParameterException t) {

+		System.out.println(Constants.NAME + " test suite v" + Constants.VERSION);

+		System.out.println();

+		if (t != null) {

+			System.out.println(t.getMessage());

 			System.out.println();

 		}

+		if (jc != null) {

+			jc.usage();

+		}

+		System.exit(0);

+	}

+

+	private static String buildDivider(char c, int length) {

+		StringBuilder sb = new StringBuilder();

+		for (int i = 0; i < length; i++) {

+			sb.append(c);

+		}

+		return sb.toString();

 	}

 

 	/**

@@ -101,6 +283,8 @@ public class IciqlSuite {
 	private static class TestDb {

 		final String name;

 		final String url;

+		String version;

+		long runtime;

 

 		TestDb(String name, String url) {

 			this.name = name;

@@ -108,14 +292,27 @@ public class IciqlSuite {
 		}

 

 		String getVersion() {

-			try {

-				Db db = Db.open(url, "sa", "sa");

-				String version = db.getConnection().getMetaData().getDatabaseProductVersion();

-				db.close();

-				return version;

-			} catch (SQLException s) {

+			if (version == null) {

+				try {

+					Db db = Db.open(url, username, password);

+					version = db.getConnection().getMetaData().getDatabaseProductVersion();

+					db.close();

+					return version;

+				} catch (SQLException s) {

+					version = "";

+				}

 			}

-			return "";

+			return version;

 		}

 	}

-}

+

+	/**

+	 * Command-line parameters for TestSuite.

+	 */

+	@Parameters(separators = " ")

+	private static class Params {

+

+		@Parameter(names = { "--outputFile" }, description = "Results text file", required = false)

+		public String outputFile;

+	}

+}
\ No newline at end of file
diff --git a/tests/com/iciql/test/ModelsTest.java b/tests/com/iciql/test/ModelsTest.java
index 797c247..9bbf450 100644
--- a/tests/com/iciql/test/ModelsTest.java
+++ b/tests/com/iciql/test/ModelsTest.java
@@ -21,6 +21,7 @@ import static org.junit.Assert.assertEquals;
 import static org.junit.Assert.assertTrue;
 
 import java.sql.SQLException;
+import java.text.MessageFormat;
 import java.util.List;
 import java.util.concurrent.atomic.AtomicInteger;
 
@@ -58,7 +59,7 @@ public class ModelsTest {
 
 	@Before
 	public void setUp() {
-		db = IciqlSuite.openDb();
+		db = IciqlSuite.openNewDb();
 		db.insertAll(Product.getList());
 		db.insertAll(ProductAnnotationOnly.getList());
 		db.insertAll(ProductMixedAnnotation.getList());
@@ -71,14 +72,17 @@ public class ModelsTest {
 
 	@Test
 	public void testValidateModels() {
-		boolean isH2 = IciqlSuite.getDatabaseName(db).equals("H2");
+		boolean isH2 = IciqlSuite.isH2(db);
+		boolean isDerby = IciqlSuite.isDerby(db);
+		String schemaName = IciqlSuite.getDefaultSchema(db);
+
 		DbInspector inspector = new DbInspector(db);
-		validateModel(inspector, new Product(), 3);
-		validateModel(inspector, new ProductAnnotationOnly(), isH2 ? 2 : 3);
-		validateModel(inspector, new ProductMixedAnnotation(), isH2 ? 3 : 4);
+		validateModel(inspector, schemaName, new Product(), 3);
+		validateModel(inspector, schemaName, new ProductAnnotationOnly(), (isH2 || isDerby) ? 2 : 3);
+		validateModel(inspector, schemaName, new ProductMixedAnnotation(), 4);
 	}
 
-	private void validateModel(DbInspector inspector, Object o, int expected) {
+	private void validateModel(DbInspector inspector, String schemaName, Object o, int expected) {
 		List<ValidationRemark> remarks = inspector.validateModel(o, false);
 		assertTrue("validation remarks are null for " + o.getClass().getName(), remarks != null);
 		StringBuilder sb = new StringBuilder();
@@ -91,7 +95,7 @@ public class ModelsTest {
 				errorCollector.addError(new SQLException(remark.toString()));
 			}
 		}
-		assertTrue(remarks.get(0).message.equals("@IQSchema(name=PUBLIC)"));
+		assertTrue(remarks.get(0).message.equals(MessageFormat.format("@IQSchema(name={0})", schemaName)));
 		assertEquals(sb.toString(), expected, remarks.size());
 	}
 
@@ -117,12 +121,16 @@ public class ModelsTest {
 				true);
 		assertEquals(1, models.size());
 		// a poor test, but a start
-		String dbName = IciqlSuite.getDatabaseName(db);
+		String dbName = IciqlSuite.getDatabaseEngineName(db);
 		if (dbName.equals("H2")) {
 			assertEquals(1478, models.get(0).length());
 		} else if (dbName.startsWith("HSQL")) {
 			// HSQL uses Double instead of Float
 			assertEquals(1479, models.get(0).length());
+		} else if (dbName.equals("Apache Derby")) {
+			// Derby uses java.sql.Timestamp not java.util.Date
+			// Derby uses username as schema name
+			assertEquals(1489, models.get(0).length());
 		}
 	}
 
@@ -143,7 +151,7 @@ public class ModelsTest {
 
 	@Test
 	public void testTableUpgrade() {
-		Db db = IciqlSuite.openDb();
+		Db db = IciqlSuite.openNewDb();
 
 		// insert first, this will create version record automatically
 		List<SupportedTypes> original = SupportedTypes.createList();
diff --git a/tests/com/iciql/test/PrimitivesTest.java b/tests/com/iciql/test/PrimitivesTest.java
index 9c214b1..be2b726 100644
--- a/tests/com/iciql/test/PrimitivesTest.java
+++ b/tests/com/iciql/test/PrimitivesTest.java
@@ -34,7 +34,7 @@ public class PrimitivesTest {
 

 	@Test

 	public void testPrimitives() {

-		Db db = IciqlSuite.openDb();

+		Db db = IciqlSuite.openNewDb();

 

 		// insert random models in reverse order

 		List<PrimitivesModel> models = PrimitivesModel.getList();

@@ -74,11 +74,11 @@ public class PrimitivesTest {
 		List<PrimitivesModel> list = db.from(p).orderBy(p.myLong).select();

 		assertEquals(models.size(), list.size());

 		assertEquals("[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]", list.toString());

-		

+

 		// test model update

 		retrievedModel.myInteger = 1337;

 		assertEquals(1, db.update(retrievedModel));

-		assertEquals(1, db.delete(retrievedModel));		

+		assertEquals(1, db.delete(retrievedModel));

 

 		db.close();

 	}

diff --git a/tests/com/iciql/test/RuntimeQueryTest.java b/tests/com/iciql/test/RuntimeQueryTest.java
index fa3ee1c..bf6f20d 100644
--- a/tests/com/iciql/test/RuntimeQueryTest.java
+++ b/tests/com/iciql/test/RuntimeQueryTest.java
@@ -34,7 +34,7 @@ public class RuntimeQueryTest {
 

 	@Test

 	public void testRuntimeQuery() {

-		Db db = IciqlSuite.openDb();

+		Db db = IciqlSuite.openNewDb();

 		db.insertAll(Product.getList());

 

 		Product p = new Product();

@@ -50,14 +50,15 @@ public class RuntimeQueryTest {
 

 	@Test

 	public void testExecuteQuery() throws SQLException {

-		Db db = IciqlSuite.openDb();

+		Db db = IciqlSuite.openNewDb();

 		db.insertAll(Product.getList());

 

 		// test plain statement

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

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

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

 		assertEquals(1, products.size());

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

-		

+

 		// test prepared statement

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

 		assertEquals(1, products.size());

@@ -65,10 +66,10 @@ public class RuntimeQueryTest {
 

 		db.close();

 	}

-	

+

 	@Test

 	public void testBuildObjects() throws SQLException {

-		Db db = IciqlSuite.openDb();

+		Db db = IciqlSuite.openNewDb();

 		db.insertAll(Product.getList());

 

 		// test plain statement

@@ -78,7 +79,7 @@ public class RuntimeQueryTest {
 

 		assertEquals(1, products.size());

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

-		

+

 		// test prepared statement

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

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

diff --git a/tests/com/iciql/test/SamplesTest.java b/tests/com/iciql/test/SamplesTest.java
index 30723bb..f16672a 100644
--- a/tests/com/iciql/test/SamplesTest.java
+++ b/tests/com/iciql/test/SamplesTest.java
@@ -60,7 +60,7 @@ public class SamplesTest {
 

 	@Before

 	public void setUp() {

-		db = IciqlSuite.openDb();

+		db = IciqlSuite.openNewDb();

 		db.insertAll(Product.getList());

 		db.insertAll(Customer.getList());

 		db.insertAll(Order.getList());

@@ -278,7 +278,7 @@ public class SamplesTest {
 	@Test

 	public void testSum() {

 		Product p = new Product();

-		Long sum = db.from(p).selectFirst(sum(p.unitsInStock));

+		Number sum = db.from(p).selectFirst(sum(p.unitsInStock));

 		assertEquals(323, sum.intValue());

 		Double sumPrice = db.from(p).selectFirst(sum(p.unitPrice));

 		assertEquals(313.35, sumPrice.doubleValue(), 0.001);

diff --git a/tests/com/iciql/test/UpdateTest.java b/tests/com/iciql/test/UpdateTest.java
index a4ee61c..717c23a 100644
--- a/tests/com/iciql/test/UpdateTest.java
+++ b/tests/com/iciql/test/UpdateTest.java
@@ -41,7 +41,7 @@ public class UpdateTest {
 
 	@Before
 	public void setUp() throws Exception {
-		db = IciqlSuite.openDb();
+		db = IciqlSuite.openNewDb();
 		db.insertAll(Product.getList());
 		db.insertAll(Customer.getList());
 		db.insertAll(Order.getList());
diff --git a/tests/com/iciql/test/models/ComplexObject.java b/tests/com/iciql/test/models/ComplexObject.java
index 294dc27..ce9b9ec 100644
--- a/tests/com/iciql/test/models/ComplexObject.java
+++ b/tests/com/iciql/test/models/ComplexObject.java
@@ -17,6 +17,7 @@
 
 package com.iciql.test.models;
 
+import static com.iciql.Define.length;
 import static com.iciql.Define.primaryKey;
 
 import java.math.BigDecimal;
@@ -55,6 +56,7 @@ public class ComplexObject implements Iciql {
 
 	public void defineIQ() {
 		primaryKey(id);
+		length(name, 25);
 	}
 
 	public static List<ComplexObject> getList() {
diff --git a/tests/com/iciql/test/models/Customer.java b/tests/com/iciql/test/models/Customer.java
index a0bbb7e..ae8e40d 100644
--- a/tests/com/iciql/test/models/Customer.java
+++ b/tests/com/iciql/test/models/Customer.java
@@ -31,7 +31,7 @@ public class Customer {
 

 	@IQColumn(length = 25)

 	public String customerId;

-	

+

 	@IQColumn(length = 2)

 	public String region;

 

diff --git a/tests/com/iciql/test/models/DefaultValuesModel.java b/tests/com/iciql/test/models/DefaultValuesModel.java
index ab09186..cb3b421 100644
--- a/tests/com/iciql/test/models/DefaultValuesModel.java
+++ b/tests/com/iciql/test/models/DefaultValuesModel.java
@@ -31,14 +31,14 @@ public class DefaultValuesModel {
 

 	@IQColumn(primaryKey = true, autoIncrement = true)

 	public Long myLong;

-	

+

 	@SuppressWarnings("deprecation")

 	@IQColumn

 	public Date myDate = new Date(100, 7, 1);

-	

+

 	@IQColumn

 	public Integer myInteger = 12345;

-	

+

 	@IQColumn

 	public Tree myEnumIdTree = Tree.WALNUT;

 

diff --git a/tests/com/iciql/test/models/Order.java b/tests/com/iciql/test/models/Order.java
index 0755e95..1fa9097 100644
--- a/tests/com/iciql/test/models/Order.java
+++ b/tests/com/iciql/test/models/Order.java
@@ -36,7 +36,7 @@ import com.iciql.Iciql;
 public class Order implements Iciql {

 	public String customerId;

 	public Integer orderId;

-	public Date orderDate;	

+	public Date orderDate;

 	public BigDecimal total;

 

 	public Order(String customerId, Integer orderId, String total, String orderDate) {

@@ -60,9 +60,12 @@ public class Order implements Iciql {
 

 	public static List<Order> getList() {

 		Order[] list = { new Order("ALFKI", 10702, "330.00", "2007-01-02"),

-				new Order("ALFKI", 10952, "471.20", "2007-02-03"), new Order("ANATR", 10308, "88.80", "2007-01-03"),

-				new Order("ANATR", 10625, "479.75", "2007-03-03"), new Order("ANATR", 10759, "320.00", "2007-04-01"),

-				new Order("ANTON", 10365, "403.20", "2007-02-13"), new Order("ANTON", 10682, "375.50", "2007-03-13"),

+				new Order("ALFKI", 10952, "471.20", "2007-02-03"),

+				new Order("ANATR", 10308, "88.80", "2007-01-03"),

+				new Order("ANATR", 10625, "479.75", "2007-03-03"),

+				new Order("ANATR", 10759, "320.00", "2007-04-01"),

+				new Order("ANTON", 10365, "403.20", "2007-02-13"),

+				new Order("ANTON", 10682, "375.50", "2007-03-13"),

 				new Order("ANTON", 10355, "480.00", "2007-04-11") };

 		return Arrays.asList(list);

 	}

diff --git a/tests/com/iciql/test/models/PrimitivesModel.java b/tests/com/iciql/test/models/PrimitivesModel.java
index 07c1611..0affd28 100644
--- a/tests/com/iciql/test/models/PrimitivesModel.java
+++ b/tests/com/iciql/test/models/PrimitivesModel.java
@@ -81,7 +81,7 @@ public class PrimitivesModel {
 		}

 		return list;

 	}

-	

+

 	@Override

 	public String toString() {

 		return String.valueOf(myLong);

diff --git a/tests/com/iciql/test/models/Product.java b/tests/com/iciql/test/models/Product.java
index 35eaa67..106e51d 100644
--- a/tests/com/iciql/test/models/Product.java
+++ b/tests/com/iciql/test/models/Product.java
@@ -59,7 +59,8 @@ public class Product implements Iciql {
 		index(productName, category);

 	}

 

-	private static Product create(int productId, String productName, String category, double unitPrice, int unitsInStock) {

+	private static Product create(int productId, String productName, String category, double unitPrice,

+			int unitsInStock) {

 		return new Product(productId, productName, category, unitPrice, unitsInStock);

 	}

 

@@ -71,7 +72,8 @@ public class Product implements Iciql {
 				create(6, "Grandma's Boysenberry Spread", "Condiments", 25.0, 120),

 				create(7, "Uncle Bob's Organic Dried Pears", "Produce", 30.0, 15),

 				create(8, "Northwoods Cranberry Sauce", "Condiments", 40.0, 6),

-				create(9, "Mishi Kobe Niku", "Meat/Poultry", 97.0, 29), create(10, "Ikura", "Seafood", 31.0, 31), };

+				create(9, "Mishi Kobe Niku", "Meat/Poultry", 97.0, 29),

+				create(10, "Ikura", "Seafood", 31.0, 31), };

 

 		return Arrays.asList(list);

 	}

diff --git a/tests/com/iciql/test/models/ProductInheritedAnnotation.java b/tests/com/iciql/test/models/ProductInheritedAnnotation.java
index 0a63aae..112f4ef 100644
--- a/tests/com/iciql/test/models/ProductInheritedAnnotation.java
+++ b/tests/com/iciql/test/models/ProductInheritedAnnotation.java
@@ -42,7 +42,8 @@ public class ProductInheritedAnnotation extends ProductMixedAnnotation {
 
 	private static ProductInheritedAnnotation create(int productId, String productName, String category,
 			double unitPrice, int unitsInStock, String mappedField) {
-		return new ProductInheritedAnnotation(productId, productName, category, unitPrice, unitsInStock, mappedField);
+		return new ProductInheritedAnnotation(productId, productName, category, unitPrice, unitsInStock,
+				mappedField);
 	}
 
 	public static List<ProductInheritedAnnotation> getData() {
diff --git a/tests/com/iciql/test/models/ProductMixedAnnotation.java b/tests/com/iciql/test/models/ProductMixedAnnotation.java
index 646f75c..703f8cd 100644
--- a/tests/com/iciql/test/models/ProductMixedAnnotation.java
+++ b/tests/com/iciql/test/models/ProductMixedAnnotation.java
@@ -20,7 +20,8 @@ package com.iciql.test.models;
 import java.util.Arrays;
 import java.util.List;
 
-import com.iciql.Iciql.IQColumn;
+import com.iciql.Define;
+import com.iciql.Iciql;
 import com.iciql.Iciql.IQIndex;
 import com.iciql.Iciql.IQTable;
 
@@ -29,8 +30,8 @@ import com.iciql.Iciql.IQTable;
  */
 
 @IQTable(annotationsOnly = false)
-@IQIndex({"name", "cat" })
-public class ProductMixedAnnotation {
+@IQIndex({ "name", "cat" })
+public class ProductMixedAnnotation implements Iciql {
 
 	public Double unitPrice;
 	public Integer unitsInStock;
@@ -59,9 +60,10 @@ public class ProductMixedAnnotation {
 		this.mappedField = mappedField;
 	}
 
-	private static ProductMixedAnnotation create(int productId, String productName, String category, double unitPrice,
-			int unitsInStock, String mappedField) {
-		return new ProductMixedAnnotation(productId, productName, category, unitPrice, unitsInStock, mappedField);
+	private static ProductMixedAnnotation create(int productId, String productName, String category,
+			double unitPrice, int unitsInStock, String mappedField) {
+		return new ProductMixedAnnotation(productId, productName, category, unitPrice, unitsInStock,
+				mappedField);
 	}
 
 	public static List<ProductMixedAnnotation> getList() {
@@ -91,4 +93,8 @@ public class ProductMixedAnnotation {
 		return productName;
 	}
 
+	@Override
+	public void defineIQ() {
+		Define.length(mappedField, 25);
+	}
 }
diff --git a/tests/com/iciql/test/models/SupportedTypes.java b/tests/com/iciql/test/models/SupportedTypes.java
index 4bd497d..596af74 100644
--- a/tests/com/iciql/test/models/SupportedTypes.java
+++ b/tests/com/iciql/test/models/SupportedTypes.java
@@ -124,13 +124,15 @@ public class SupportedTypes {
 
 	public static List<SupportedTypes> createList() {
 		List<SupportedTypes> list = Utils.newArrayList();
+		long now = System.currentTimeMillis();
+		long oneday = 24 * 60 * 60 * 1000L;
 		for (int i = 0; i < 10; i++) {
-			list.add(randomValue());
+			list.add(randomValue(now - (i * oneday)));
 		}
 		return list;
 	}
 
-	static SupportedTypes randomValue() {
+	static SupportedTypes randomValue(long time) {
 		Random rand = new Random();
 		SupportedTypes s = new SupportedTypes();
 		s.myBool = new Boolean(rand.nextBoolean());
@@ -144,10 +146,10 @@ public class SupportedTypes {
 		// scale must match annotation
 		s.myBigDecimal = s.myBigDecimal.setScale(5, RoundingMode.UP);
 		s.myString = Long.toHexString(rand.nextLong());
-		s.myUtilDate = new java.util.Date(rand.nextLong());
-		s.mySqlDate = new java.sql.Date(rand.nextLong());
-		s.mySqlTime = new java.sql.Time(rand.nextLong());
-		s.mySqlTimestamp = new java.sql.Timestamp(rand.nextLong());
+		s.myUtilDate = new java.util.Date(time);
+		s.mySqlDate = new java.sql.Date(time);
+		s.mySqlTime = new java.sql.Time(time);
+		s.mySqlTimestamp = new java.sql.Timestamp(time);
 		s.myBlob = new byte[] { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
 		s.myDefaultFlower = Flower.DAFFODIL;
 		s.myFavoriteFlower = Flower.MUM;
@@ -181,7 +183,7 @@ public class SupportedTypes {
 		same &= myOtherFavoriteTree.equals(s.myOtherFavoriteTree);
 		return same;
 	}
-	
+
 	/**
 	 * This class demonstrates the table upgrade.
 	 */