Launch H2 and HSQL servers in test suite and include tcp benchmarks.

11 files changed, 122 insertions, 105 deletions

diff --git a/docs/03_performance.mkd b/docs/03_performance.mkd
index 12c07a1..377d222 100644
--- a/docs/03_performance.mkd
+++ b/docs/03_performance.mkd
@@ -9,7 +9,7 @@ Performance of iciql statement generation is not currently benchmarked.
 

 ### iciql+database performance comparison

 

-The following data was generated by running the *single-threaded* iciql test suite.  All database connections are pooled and re-used within each execution of the test suite using [Apache Commons DBCP](http://commons.apache.org/dbcp).

+The following data was generated by running the *single-threaded* iciql test suite.  All database connections are pooled and re-used within each execution of the test suite using [Apache Commons DBCP](http://commons.apache.org/dbcp).  All tables are created as CACHED when the database distinguishes between CACHED and MEMORY tables.

 

 Connections are pooled to normalize embedded database performance with out-of-process database performance.  Some of the Java embedded database configurations have a very high startup-time penalty.  Notably, H2 is slow to open a database and its performance is substantially affected if connection pooling is not enabled to keep the embedded database open.

 

diff --git a/src/com/iciql/Iciql.java b/src/com/iciql/Iciql.java
index 7a0b58f..9ca8bf7 100644
--- a/src/com/iciql/Iciql.java
+++ b/src/com/iciql/Iciql.java
@@ -353,7 +353,7 @@ public interface Iciql {
 		/**

 		 * If true, this table is created as a memory table where data is

 		 * persistent, but index data is kept in main memory. Valid only for H2

-		 * databases. Default: false.

+		 * and HSQL databases. Default: false.

 		 */

 		boolean memoryTable() default false;

 	}

diff --git a/src/com/iciql/SQLDialect.java b/src/com/iciql/SQLDialect.java
index 6e6be25..7c29d61 100644
--- a/src/com/iciql/SQLDialect.java
+++ b/src/com/iciql/SQLDialect.java
@@ -118,27 +118,6 @@ public interface SQLDialect {
 	void appendLimitOffset(SQLStatement stat, long limit, long offset);
 
 	/**
-	 * Whether memory tables are supported.
-	 * 
-	 * @return true if they are
-	 */
-	boolean supportsMemoryTables();
-
-	/**
-	 * Whether IF NOT EXISTS notation is supported.
-	 * 
-	 * @return true if they are
-	 */
-	boolean supportsIfNotExists();
-
-	/**
-	 * Whether LIMIT/OFFSET notation is supported.
-	 * 
-	 * @return true if they are
-	 */
-	boolean supportsLimitOffset();
-
-	/**
 	 * Returns the preferred DATETIME class for the database.
 	 * <p>
 	 * Either java.util.Date or java.sql.Timestamp
diff --git a/src/com/iciql/SQLDialectDefault.java b/src/com/iciql/SQLDialectDefault.java
index 4814fdd..58848c1 100644
--- a/src/com/iciql/SQLDialectDefault.java
+++ b/src/com/iciql/SQLDialectDefault.java
@@ -69,21 +69,6 @@ public class SQLDialectDefault implements SQLDialect {
 	}

 

 	@Override

-	public boolean supportsMemoryTables() {

-		return false;

-	}

-

-	@Override

-	public boolean supportsIfNotExists() {

-		return true;

-	}

-

-	@Override

-	public boolean supportsLimitOffset() {

-		return true;

-	}

-

-	@Override

 	public String prepareTableName(String schemaName, String tableName) {

 		if (StringUtils.isNullOrEmpty(schemaName)) {

 			return tableName;

@@ -104,19 +89,15 @@ public class SQLDialectDefault implements SQLDialect {
 		return;

 	}

 

+	protected <T> String prepareCreateTable(TableDefinition<T> def) {

+		return "CREATE TABLE";

+	}

+

 	@Override

 	public <T> void prepareCreateTable(SQLStatement stat, TableDefinition<T> def) {

-		StatementBuilder buff;

-		if (def.memoryTable && supportsMemoryTables()) {

-			buff = new StatementBuilder("CREATE MEMORY TABLE ");

-		} else {

-			buff = new StatementBuilder("CREATE TABLE ");

-		}

-

-		if (supportsIfNotExists()) {

-			buff.append("IF NOT EXISTS ");

-		}

-

+		StatementBuilder buff = new StatementBuilder();

+		buff.append(prepareCreateTable(def));

+		buff.append(" ");

 		buff.append(prepareTableName(def.schemaName, def.tableName)).append('(');

 

 		boolean hasIdentityColumn = false;

diff --git a/src/com/iciql/SQLDialectDerby.java b/src/com/iciql/SQLDialectDerby.java
index fd06844..f954a7c 100644
--- a/src/com/iciql/SQLDialectDerby.java
+++ b/src/com/iciql/SQLDialectDerby.java
@@ -38,28 +38,15 @@ public class SQLDialectDerby extends SQLDialectDefault {
 	}

 

 	@Override

-	public boolean supportsMemoryTables() {

-		return false;

-	}

-

-	@Override

-	public boolean supportsIfNotExists() {

-		return false;

-	}

-

-	@Override

-	public boolean supportsLimitOffset() {

-		// FETCH/OFFSET added in 10.5

-		return databaseVersion >= 10.5f;

-	}

-

-	@Override

 	public void appendLimitOffset(SQLStatement stat, long limit, long offset) {

-		if (offset > 0) {

-			stat.appendSQL(" OFFSET " + offset + (offset == 1 ? " ROW" : " ROWS"));

-		}

-		if (limit > 0) {

-			stat.appendSQL(" FETCH NEXT " + limit + (limit == 1 ? " ROW" : " ROWS") + " ONLY");

+		// FETCH/OFFSET added in 10.5

+		if (databaseVersion >= 10.5f) {

+			if (offset > 0) {

+				stat.appendSQL(" OFFSET " + offset + (offset == 1 ? " ROW" : " ROWS"));

+			}

+			if (limit > 0) {

+				stat.appendSQL(" FETCH NEXT " + limit + (limit == 1 ? " ROW" : " ROWS") + " ONLY");

+			}

 		}

 	}

 

diff --git a/src/com/iciql/SQLDialectH2.java b/src/com/iciql/SQLDialectH2.java
index 6f74b45..1da45f6 100644
--- a/src/com/iciql/SQLDialectH2.java
+++ b/src/com/iciql/SQLDialectH2.java
@@ -25,25 +25,33 @@ import com.iciql.util.StatementBuilder;
  */

 public class SQLDialectH2 extends SQLDialectDefault {

 

+	/**

+	 * CACHED tables are created by default. MEMORY tables are created upon

+	 * request.

+	 */

 	@Override

-	public boolean supportsMemoryTables() {

-		return true;

+	protected <T> String prepareCreateTable(TableDefinition<T> def) {

+		if (def.memoryTable) {

+			return "CREATE MEMORY TABLE IF NOT EXISTS";

+		} else {

+			return "CREATE CACHED TABLE IF NOT EXISTS";

+		}

 	}

 

 	@Override

-	protected boolean prepareColumnDefinition(StatementBuilder buff, String dataType, boolean isAutoIncrement,

-			boolean isPrimaryKey) {

+	protected boolean prepareColumnDefinition(StatementBuilder buff, String dataType,

+			boolean isAutoIncrement, boolean isPrimaryKey) {

 		String convertedType = convertSqlType(dataType);

 		boolean isIdentity = false;

 		if (isIntegerType(dataType)) {

 			if (isAutoIncrement && isPrimaryKey) {

 				buff.append("IDENTITY");

-				isIdentity =  true;

+				isIdentity = true;

 			} else if (isAutoIncrement) {

 				buff.append(convertedType);

 				buff.append(" AUTO_INCREMENT");

 			} else {

-				buff.append(convertedType);	

+				buff.append(convertedType);

 			}

 		} else {

 			buff.append(convertedType);

diff --git a/src/com/iciql/SQLDialectHSQL.java b/src/com/iciql/SQLDialectHSQL.java
index 3d72ac2..9975be6 100644
--- a/src/com/iciql/SQLDialectHSQL.java
+++ b/src/com/iciql/SQLDialectHSQL.java
@@ -26,14 +26,22 @@ import com.iciql.util.StatementBuilder;
  */

 public class SQLDialectHSQL extends SQLDialectDefault {

 

+	/**

+	 * CACHED tables are created by default. MEMORY tables are created upon

+	 * request.

+	 */

 	@Override

-	public boolean supportsMemoryTables() {

-		return true;

+	protected <T> String prepareCreateTable(TableDefinition<T> def) {

+		if (def.memoryTable) {

+			return "CREATE MEMORY TABLE IF NOT EXISTS";

+		} else {

+			return "CREATE CACHED TABLE IF NOT EXISTS";

+		}

 	}

 

 	@Override

-	protected boolean prepareColumnDefinition(StatementBuilder buff, String dataType, boolean isAutoIncrement,

-			boolean isPrimaryKey) {

+	protected boolean prepareColumnDefinition(StatementBuilder buff, String dataType,

+			boolean isAutoIncrement, boolean isPrimaryKey) {

 		boolean isIdentity = false;

 		String convertedType = convertSqlType(dataType);

 		buff.append(convertedType);

diff --git a/src/com/iciql/SQLDialectMySQL.java b/src/com/iciql/SQLDialectMySQL.java
index 0b2acf4..7fa1fa9 100644
--- a/src/com/iciql/SQLDialectMySQL.java
+++ b/src/com/iciql/SQLDialectMySQL.java
@@ -33,6 +33,10 @@ public class SQLDialectMySQL extends SQLDialectDefault {
 	}

 

 	@Override

+	protected <T> String prepareCreateTable(TableDefinition<T> def) {

+		return "CREATE TABLE IF NOT EXISTS";

+	}

+	@Override

 	public String prepareColumnName(String name) {

 		return "`" + name + "`";

 	}

diff --git a/src/com/iciql/SQLDialectPostgreSQL.java b/src/com/iciql/SQLDialectPostgreSQL.java
index 0874a49..5b3ec71 100644
--- a/src/com/iciql/SQLDialectPostgreSQL.java
+++ b/src/com/iciql/SQLDialectPostgreSQL.java
@@ -30,11 +30,6 @@ public class SQLDialectPostgreSQL extends SQLDialectDefault {
 	}

 

 	@Override

-	public boolean supportsIfNotExists() {

-		return false;

-	}

-

-	@Override

 	public String convertSqlType(String sqlType) {

 		if ("DOUBLE".equals(sqlType)) {

 			return "DOUBLE PRECISION";

diff --git a/tests/com/iciql/test/IciqlSuite.java b/tests/com/iciql/test/IciqlSuite.java
index d68e146..88b8f33 100644
--- a/tests/com/iciql/test/IciqlSuite.java
+++ b/tests/com/iciql/test/IciqlSuite.java
@@ -32,6 +32,7 @@ import org.apache.commons.dbcp.DriverManagerConnectionFactory;
 import org.apache.commons.dbcp.PoolableConnectionFactory;

 import org.apache.commons.dbcp.PoolingDataSource;

 import org.apache.commons.pool.impl.GenericObjectPool;

+import org.hsqldb.persist.HsqlProperties;

 import org.junit.Assert;

 import org.junit.runner.JUnitCore;

 import org.junit.runner.Result;

@@ -89,24 +90,24 @@ public class IciqlSuite {
 	private static final TestDb[] TEST_DBS = {

 			new TestDb("H2", true, true, "jdbc:h2:mem:iciql"),

 			new TestDb("H2", true, false, "jdbc:h2:file:testdbs/h2/iciql"),

+			new TestDb("H2", false, false, "jdbc:h2:tcp://localhost/"

+					+ new File(System.getProperty("user.dir")).getAbsolutePath() + "/testdbs/h2tcp/iciql"),

 			new TestDb("HSQL", true, true, "jdbc:hsqldb:mem:iciql"),

 			new TestDb("HSQL", true, false, "jdbc:hsqldb:file:testdbs/hsql/iciql"),

+			new TestDb("HSQL", false, false, "jdbc:hsqldb:hsql://localhost/iciql"),

 			new TestDb("Derby", true, true, "jdbc:derby:memory:iciql;create=true"),

 			new TestDb("Derby", true, false, "jdbc:derby:directory:testdbs/derby/iciql;create=true"),

-			new TestDb("MySQL", false, false, "jdbc:mysql://localhost:3306/iciql"),

-			new TestDb("PostgreSQL", false, false, "jdbc:postgresql://localhost:5432/iciql") };

+			new TestDb("MySQL", false, false, "jdbc:mysql://localhost:7000/iciql", "sa", "sa"),

+			new TestDb("PostgreSQL", false, false, "jdbc:postgresql://localhost:5432/iciql", "sa", "sa") };

 

 	private static final TestDb DEFAULT_TEST_DB = TEST_DBS[0];

 

 	private static final PrintStream ERR = System.err;

 

-	private static String username = "sa";

-

-	private static String password = "sa";

-

 	private static PrintStream out = System.out;

 

-	private static Map<String, PoolableConnectionFactory> connectionFactories = Utils.newSynchronizedHashMap();

+	private static Map<String, PoolableConnectionFactory> connectionFactories = Utils

+			.newSynchronizedHashMap();

 

 	private static Map<String, PoolingDataSource> dataSources = Utils.newSynchronizedHashMap();

 

@@ -134,15 +135,15 @@ public class IciqlSuite {
 	 * @return a fresh Db object

 	 */

 	public static Db openNewDb() {

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

-		if (testUrl == null) {

-			testUrl = DEFAULT_TEST_DB.url;

-		}

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

+		String testUser = System.getProperty("iciql.user", DEFAULT_TEST_DB.username);

+		String testPassword = System.getProperty("iciql.password", DEFAULT_TEST_DB.password);

+

 		Db db = null;

 		PoolingDataSource dataSource = dataSources.get(testUrl);

 		if (dataSource == null) {

-			ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(testUrl, username,

-					password);

+			ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(testUrl, testUser,

+					testPassword);

 			GenericObjectPool pool = new GenericObjectPool();

 			pool.setWhenExhaustedAction(GenericObjectPool.WHEN_EXHAUSTED_GROW);

 			PoolableConnectionFactory factory = new PoolableConnectionFactory(connectionFactory, pool, null,

@@ -178,11 +179,10 @@ public class IciqlSuite {
 	 * @return the current database

 	 */

 	public static Db openCurrentDb() {

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

-		if (testUrl == null) {

-			testUrl = DEFAULT_TEST_DB.url;

-		}

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

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

+		String testUser = System.getProperty("iciql.user", DEFAULT_TEST_DB.username);

+		String testPassword = System.getProperty("iciql.password", DEFAULT_TEST_DB.password);

+		return Db.open(testUrl, testUser, testPassword);

 	}

 

 	/**

@@ -239,7 +239,7 @@ public class IciqlSuite {
 	public static String getDefaultSchema(Db db) {

 		if (isDerby(db)) {

 			// Derby sets default schema name to username

-			return username.toUpperCase();

+			return "SA";

 		} else if (isMySQL(db)) {

 			// MySQL does not have schemas

 			return null;

@@ -272,6 +272,10 @@ public class IciqlSuite {
 

 		deleteRecursively(new File("testdbs"));

 

+		// Start the HSQL and H2 servers in-process

+		org.hsqldb.Server hsql = startHSQL();

+		org.h2.tools.Server h2 = startH2();

+

 		// Statement logging

 		final FileWriter statementWriter;

 		if (StringUtils.isNullOrEmpty(params.sqlStatementsFile)) {

@@ -342,9 +346,15 @@ public class IciqlSuite {
 				out.println("Skipping.  Could not find " + testDb.url);

 				out.println();

 			} else {

-				// Test database

+				// Setup system properties

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

+				System.setProperty("iciql.user", testDb.username);

+				System.setProperty("iciql.password", testDb.password);

+

+				// Test database

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

+

+				// Report results

 				testDb.runtime = result.getRunTime();

 				if (testDb.runtime < quickestDatabase) {

 					quickestDatabase = testDb.runtime;

@@ -410,6 +420,8 @@ public class IciqlSuite {
 		if (statementWriter != null) {

 			statementWriter.close();

 		}

+		hsql.stop();

+		h2.stop();

 		System.exit(0);

 	}

 

@@ -457,6 +469,40 @@ public class IciqlSuite {
 	}

 

 	/**

+	 * Start an HSQL tcp server.

+	 * 

+	 * @return an HSQL server instance

+	 * @throws Exception

+	 */

+	private static org.hsqldb.Server startHSQL() throws Exception {

+		HsqlProperties p = new HsqlProperties();

+		String db = new File(System.getProperty("user.dir")).getAbsolutePath() + "/testdbs/hsqltcp/iciql";

+		p.setProperty("server.database.0", "file:" + db);

+		p.setProperty("server.dbname.0", "iciql");

+		// set up the rest of properties

+

+		// alternative to the above is

+		org.hsqldb.Server server = new org.hsqldb.Server();		

+		server.setProperties(p);

+		server.setLogWriter(null);

+		server.setErrWriter(null);

+		server.start();

+		return server;

+	}

+

+	/**

+	 * Start the H2 tcp server.

+	 * 

+	 * @return an H2 server instance

+	 * @throws Exception

+	 */

+	private static org.h2.tools.Server startH2() throws Exception {

+		org.h2.tools.Server server = org.h2.tools.Server.createTcpServer();

+		server.start();

+		return server;

+	}

+

+	/**

 	 * Represents a test database url.

 	 */

 	private static class TestDb implements Comparable<TestDb> {

@@ -464,15 +510,23 @@ public class IciqlSuite {
 		boolean isEmbedded;

 		boolean isMemory;

 		final String url;

+		final String username;

+		final String password;

 		String version;

 		long runtime;

 		long statements;

 

 		TestDb(String name, boolean isEmbedded, boolean isMemory, String url) {

+			this(name, isEmbedded, isMemory, url, "sa", "");

+		}

+

+		TestDb(String name, boolean isEmbedded, boolean isMemory, String url, String username, String password) {

 			this.name = name;

 			this.isEmbedded = isEmbedded;

 			this.isMemory = isMemory;

 			this.url = url;

+			this.username = username;

+			this.password = password;

 		}

 

 		double getRuntime() {

diff --git a/tests/com/iciql/test/UUIDTest.java b/tests/com/iciql/test/UUIDTest.java
index 9b88cb8..bb09c9f 100644
--- a/tests/com/iciql/test/UUIDTest.java
+++ b/tests/com/iciql/test/UUIDTest.java
@@ -69,6 +69,7 @@ public class UUIDTest {
 
 		UUIDRecord second = db.from(u).where(u.uuid).is(originals.get(1).uuid).selectFirst();
 		assertTrue(originals.get(1).equivalentTo(second));
+		db.dropTable(UUIDRecord.class);
 	}
 
 	/**