Added support for HSQL database. Revised dialects some more.

Moved CREATE TABLE and CREATE INDEX statement generation to the dialect.
Added DECIMAL(length, scale) support.
Improved automatic dialect detection.
Unspecified length string is now CLOB instead of TEXT.
Boolean now maps to BOOLEAN instead of BIT.
Expressions on unmapped fields will throw an IciqlException.
Improved exception reporting.

19 files changed, 449 insertions, 217 deletions

diff --git a/src/com/iciql/Db.java b/src/com/iciql/Db.java
index c7801c2..a8c0b12 100644
--- a/src/com/iciql/Db.java
+++ b/src/com/iciql/Db.java
@@ -73,6 +73,7 @@ public class Db {
 		// 2. DatabaseMetaData.getDatabaseProductName()

 		DIALECTS.put("H2", SQLDialectH2.class);

 		DIALECTS.put("MySQL", SQLDialectMySQL.class);

+		DIALECTS.put("HSQL Database Engine", SQLDialectHSQL.class);

 	}

 

 	private Db(Connection conn) {

@@ -83,7 +84,7 @@ public class Db {
 			data = conn.getMetaData();

 			databaseName = data.getDatabaseProductName();

 		} catch (SQLException s) {

-			throw new IciqlException("Failed to retrieve database metadata!", s);

+			throw new IciqlException(s, "failed to retrieve database metadata!");

 		}

 		dialect = getDialect(databaseName, conn.getClass().getName());

 		dialect.configureDialect(databaseName, data);

@@ -140,7 +141,7 @@ public class Db {
 			Connection conn = JdbcUtils.getConnection(null, url, user, password);

 			return new Db(conn);

 		} catch (SQLException e) {

-			throw convert(e);

+			throw new IciqlException(e);

 		}

 	}

 

@@ -156,7 +157,7 @@ public class Db {
 		try {

 			return new Db(ds.getConnection());

 		} catch (SQLException e) {

-			throw convert(e);

+			throw new IciqlException(e);

 		}

 	}

 

@@ -172,14 +173,10 @@ public class Db {
 			Connection conn = JdbcUtils.getConnection(null, url, prop);

 			return new Db(conn);

 		} catch (SQLException e) {

-			throw convert(e);

+			throw new IciqlException(e);

 		}

 	}

 

-	private static Error convert(Exception e) {

-		return new Error(e);

-	}

-

 	public <T> void insert(T t) {

 		Class<?> clazz = t.getClass();

 		define(clazz).createTableIfRequired(this).insert(this, t, false);

@@ -191,18 +188,15 @@ public class Db {
 	}

 

 	/**

-	 * Merge usually INSERTS if the record does not exist or UPDATES the record

-	 * if it does exist. Not all databases support MERGE and the syntax varies

-	 * with the database.

+	 * Merge INSERTS if the record does not exist or UPDATES the record if it

+	 * does exist. Not all databases support MERGE and the syntax varies with

+	 * the database.

 	 * 

 	 * If the dialect does not support merge an IciqlException will be thrown.

 	 * 

 	 * @param t

 	 */

 	public <T> void merge(T t) {

-		if (!getDialect().supportsMerge()) {

-			throw new IciqlException("Merge is not supported by this SQL dialect");

-		}

 		Class<?> clazz = t.getClass();

 		define(clazz).createTableIfRequired(this).merge(this, t);

 	}

@@ -383,13 +377,14 @@ public class Db {
 	}

 

 	PreparedStatement prepare(String sql, boolean returnGeneratedKeys) {

+		IciqlException.checkUnmappedField(sql);

 		try {

 			if (returnGeneratedKeys) {

 				return conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

 			}

 			return conn.prepareStatement(sql);

 		} catch (SQLException e) {

-			throw new IciqlException(e);

+			throw IciqlException.fromSQL(sql, e);

 		}

 	}

 

diff --git a/src/com/iciql/DbVersion.java b/src/com/iciql/DbVersion.java
index 8ccf66f..6270e14 100644
--- a/src/com/iciql/DbVersion.java
+++ b/src/com/iciql/DbVersion.java
@@ -23,7 +23,7 @@ import com.iciql.Iciql.IQTable;
 /**
  * A system table to track database and table versions.
  */
-@IQTable(name = "_iq_versions", primaryKey = { "schemaName", "tableName"}, memoryTable = true)
+@IQTable(name = "iq_versions", primaryKey = { "schemaName", "tableName" }, memoryTable = true)
 public class DbVersion {
 
 	@IQColumn(length = 255)
diff --git a/src/com/iciql/Define.java b/src/com/iciql/Define.java
index d7b42ba..5e969e1 100644
--- a/src/com/iciql/Define.java
+++ b/src/com/iciql/Define.java
@@ -61,7 +61,12 @@ public class Define {
 

 	public static void length(Object column, int length) {

 		checkInDefine();

-		currentTableDefinition.setMaxLength(column, length);

+		currentTableDefinition.setLength(column, length);

+	}

+	

+	public static void scale(Object column, int scale) {

+		checkInDefine();

+		currentTableDefinition.setScale(column, scale);

 	}

 

 	public static void tableName(String tableName) {

diff --git a/src/com/iciql/Iciql.java b/src/com/iciql/Iciql.java
index 40c412a..33b954b 100644
--- a/src/com/iciql/Iciql.java
+++ b/src/com/iciql/Iciql.java
@@ -40,7 +40,7 @@ import java.lang.annotation.Target;
  * </tr>

  * <tr>

  * <td>java.lang.String</td>

- * <td>VARCHAR (length > 0) or TEXT (length == 0)</td>

+ * <td>VARCHAR (length > 0) or CLOB (length == 0)</td>

  * </tr>

  * <tr>

  * <td>java.lang.Boolean</td>

@@ -72,7 +72,7 @@ import java.lang.annotation.Target;
  * </tr>

  * <tr>

  * <td>java.math.BigDecimal</td>

- * <td>DECIMAL</td>

+ * <td>DECIMAL (length == 0)<br/>DECIMAL(length, scale)  (length > 0)</td>

  * </tr>

  * <tr>

  * <td>java.sql.Date</td>

@@ -92,7 +92,7 @@ import java.lang.annotation.Target;
  * </tr>

  * <tr>

  * <td>java.lang.Enum.name()</td>

- * <td>VARCHAR (length > 0) or TEXT (length == 0)<br/>

+ * <td>VARCHAR (length > 0) or CLOB (length == 0)<br/>

  * EnumType.NAME</td>

  * </tr>

  * <tr>

@@ -391,9 +391,12 @@ public interface Iciql {
 		boolean autoIncrement() default false;

 

 		/**

-		 * If larger than zero, it is used during the CREATE TABLE phase. It may

-		 * also be used to prevent database exceptions on INSERT and UPDATE

-		 * statements (see trim).

+		 * Length is used to define the length of a VARCHAR column or to define

+		 * the precision of a DECIMAL(precision, scale) expression.

+		 * <p>

+		 * If larger than zero, it is used during the CREATE TABLE phase. For

+		 * string values it may also be used to prevent database exceptions on

+		 * INSERT and UPDATE statements (see trim).

 		 * <p>

 		 * Any length set in define() may override this annotation setting if

 		 * the model class is not annotated with IQTable. Default: 0.

@@ -401,6 +404,15 @@ public interface Iciql {
 		int length() default 0;

 

 		/**

+		 * Scale is used during the CREATE TABLE phase to define the scale of a

+		 * DECIMAL(precision, scale) expression.

+		 * <p>

+		 * Any scale set in define() may override this annotation setting if

+		 * the model class is not annotated with IQTable. Default: 0.

+		 */

+		int scale() default 0;

+

+		/**

 		 * If true, iciql will automatically trim the string if it exceeds

 		 * length (value.substring(0, length)). Default: false.

 		 */

diff --git a/src/com/iciql/IciqlException.java b/src/com/iciql/IciqlException.java
index 1e2a890..6e55c79 100644
--- a/src/com/iciql/IciqlException.java
+++ b/src/com/iciql/IciqlException.java
@@ -16,30 +16,109 @@
 

 package com.iciql;

 

+import java.sql.SQLException;

 import java.text.MessageFormat;

+import java.util.regex.Pattern;

 

 /**

  * Iciql wraps all exceptions with this class.

  */

 public class IciqlException extends RuntimeException {

 

+	public static final int CODE_UNMAPPED_FIELD = 1;

+	public static final int CODE_DUPLICATE_KEY = 2;

+	public static final int CODE_TABLE_NOT_FOUND = 3;

+	public static final int CODE_INDEX_ALREADY_EXISTS = 4;

+

+	private static final String TOKEN_UNMAPPED_FIELD = "\\? (=|\\>|\\<|!=|\\>=|\\<=|LIKE|BETWEEN) \\?";	

+

 	private static final long serialVersionUID = 1L;

 

+	private String sql;

+

+	private int iciqlCode;

+

+	public IciqlException(Throwable t) {

+		super(t.getMessage(), t);

+		configureCode(t);

+	}

+

 	public IciqlException(String message, Object... parameters) {

 		super(parameters.length > 0 ? MessageFormat.format(message, parameters) : message);

-

 	}

 

 	public IciqlException(Throwable t, String message, Object... parameters) {

 		super(parameters.length > 0 ? MessageFormat.format(message, parameters) : message, t);

-

+		configureCode(t);

+	}

+	

+	public static void checkUnmappedField(String sql) {

+		if (Pattern.compile(IciqlException.TOKEN_UNMAPPED_FIELD).matcher(sql).find()) {

+			IciqlException e = new IciqlException("unmapped field in statement!");

+			e.sql = sql;

+			e.iciqlCode = CODE_UNMAPPED_FIELD;

+			throw e;

+		}

+	}

+	

+	public static IciqlException fromSQL(String sql, Throwable t) {

+		if (Pattern.compile(TOKEN_UNMAPPED_FIELD).matcher(sql).find()) {

+			IciqlException e = new IciqlException(t, "unmapped field in statement!");

+			e.sql = sql;

+			e.iciqlCode = CODE_UNMAPPED_FIELD;

+			return e;

+		} else {

+			IciqlException e = new IciqlException(t, t.getMessage());

+			e.sql = sql;

+			return e;

+		}

+	}

+	

+	public void setSQL(String sql) {

+		this.sql = sql;

 	}

 

-	public IciqlException(Throwable t) {

-		super(t);

+	public String getSQL() {

+		return sql;

 	}

 

-	public IciqlException(String message, Throwable t) {

-		super(message, t);

+	public int getIciqlCode() {

+		return iciqlCode;

+	}

+	

+	private void configureCode(Throwable t) {

+		if (t == null) {

+			return;

+		}

+		if (t instanceof SQLException) {

+			// http://developer.mimer.com/documentation/html_92/Mimer_SQL_Mobile_DocSet/App_Return_Codes2.html

+			SQLException s = (SQLException) t;

+			String state = s.getSQLState();

+			if ("23505".equals(state)) {

+				iciqlCode = CODE_DUPLICATE_KEY;

+			} else if ("42501".equals(state)) {

+				iciqlCode = CODE_TABLE_NOT_FOUND;

+			} else if ("42S02".equals(state)) {

+				iciqlCode = CODE_TABLE_NOT_FOUND;

+			} else if ("42504".equals(state)) {

+				iciqlCode = CODE_INDEX_ALREADY_EXISTS;

+			} else if ("42S11".equals(state)) {

+				iciqlCode = CODE_INDEX_ALREADY_EXISTS;

+			}

+		}

 	}

+	

+	@Override

+    public String toString() {

+		StringBuilder sb = new StringBuilder();

+		sb.append(getClass().getName());        

+        String message = getLocalizedMessage();

+        if (message != null) {

+        	sb.append(": ").append(message);

+        }

+        if (sql != null) {

+        	sb.append('\n').append(sql);

+        }

+        return sb.toString();

+    }

 }

diff --git a/src/com/iciql/ModelUtils.java b/src/com/iciql/ModelUtils.java
index 2e42a7d..441bca7 100644
--- a/src/com/iciql/ModelUtils.java
+++ b/src/com/iciql/ModelUtils.java
@@ -49,7 +49,7 @@ class ModelUtils {
 	static {
 		Map<Class<?>, String> m = SUPPORTED_TYPES;
 		m.put(String.class, "VARCHAR");
-		m.put(Boolean.class, "BIT");
+		m.put(Boolean.class, "BOOLEAN");
 		m.put(Byte.class, "TINYINT");
 		m.put(Short.class, "SMALLINT");
 		m.put(Integer.class, "INT");
@@ -100,8 +100,8 @@ class ModelUtils {
 		m.put("NCLOB", "VARCHAR");
 
 		// logic
-		m.put("BOOL", "BIT");
-		m.put("BOOLEAN", "BIT");
+		m.put("BIT", "BOOLEAN");
+		m.put("BOOL", "BOOLEAN");
 
 		// numeric
 		m.put("BYTE", "TINYINT");
@@ -159,19 +159,11 @@ class ModelUtils {
 				return "INT";
 			case NAME:
 			default:
-				if (fieldDef.maxLength <= 0) {
-					return "TEXT";
-				}
 				return "VARCHAR";
 			}
 		}
 		if (SUPPORTED_TYPES.containsKey(fieldClass)) {
-			String type = SUPPORTED_TYPES.get(fieldClass);
-			if (type.equals("VARCHAR") && fieldDef.maxLength <= 0) {
-				// unspecified length strings are TEXT, not VARCHAR
-				return "TEXT";
-			}
-			return type;
+			return SUPPORTED_TYPES.get(fieldClass);
 		}
 		if (!strictTypeMapping) {
 			return "VARCHAR";
diff --git a/src/com/iciql/Query.java b/src/com/iciql/Query.java
index 2e58fe5..5c2d225 100644
--- a/src/com/iciql/Query.java
+++ b/src/com/iciql/Query.java
@@ -83,7 +83,7 @@ public class Query<T> {
 			long value = rs.getLong(1);

 			return value;

 		} catch (SQLException e) {

-			throw new IciqlException(e);

+			throw IciqlException.fromSQL(stat.getSQL(), e);

 		} finally {

 			JdbcUtils.closeSilently(rs, true);

 		}

@@ -128,7 +128,7 @@ public class Query<T> {
 				result.add(item);

 			}

 		} catch (SQLException e) {

-			throw new IciqlException(e);

+			throw IciqlException.fromSQL(stat.getSQL(), e);

 		} finally {

 			JdbcUtils.closeSilently(rs, true);

 		}

@@ -204,7 +204,7 @@ public class Query<T> {
 				result.add(row);

 			}

 		} catch (SQLException e) {

-			throw new IciqlException(e);

+			throw IciqlException.fromSQL(stat.getSQL(), e);

 		} finally {

 			JdbcUtils.closeSilently(rs, true);

 		}

@@ -220,26 +220,20 @@ public class Query<T> {
 		List<X> result = Utils.newArrayList();

 		try {

 			while (rs.next()) {

-				try {

-					X value;

-					Object o = rs.getObject(1);

-					// Convert CLOB and BLOB now because we close the resultset

-					if (Clob.class.isAssignableFrom(o.getClass())) {

-						value = (X) Utils.convert(o, String.class);

-					} else if (Blob.class.isAssignableFrom(o.getClass())) {

-						value = (X) Utils.convert(o, byte[].class);

-					} else {

-						value = (X) o;

-					}

-					result.add(value);

-				} catch (IciqlException e) {

-					throw e;

-				} catch (Exception e) {

-					throw new IciqlException(e);

+				X value;

+				Object o = rs.getObject(1);

+				// Convert CLOB and BLOB now because we close the resultset

+				if (Clob.class.isAssignableFrom(o.getClass())) {

+					value = (X) Utils.convert(o, String.class);

+				} else if (Blob.class.isAssignableFrom(o.getClass())) {

+					value = (X) Utils.convert(o, byte[].class);

+				} else {

+					value = (X) o;

 				}

+				result.add(value);

 			}

-		} catch (SQLException e) {

-			throw new IciqlException(e);

+		} catch (Exception e) {

+			throw IciqlException.fromSQL(stat.getSQL(), e);

 		} finally {

 			JdbcUtils.closeSilently(rs, true);

 		}

diff --git a/src/com/iciql/QueryBetween.java b/src/com/iciql/QueryBetween.java
index de09515..8628f40 100644
--- a/src/com/iciql/QueryBetween.java
+++ b/src/com/iciql/QueryBetween.java
@@ -18,6 +18,10 @@ package com.iciql;
 

 /**

  * This class represents a "between y and z" condition.

+ * @param <T>

+ *            the return type of the query

+ * @param <A>

+ *            the incomplete condition data type

  */

 public class QueryBetween<T, A> {

 

diff --git a/src/com/iciql/SQLDialect.java b/src/com/iciql/SQLDialect.java
index 3ca2c4c..7821c3f 100644
--- a/src/com/iciql/SQLDialect.java
+++ b/src/com/iciql/SQLDialect.java
@@ -56,6 +56,14 @@ public interface SQLDialect {
 	String prepareColumnName(String name);
 
 	/**
+	 * Get the CREATE TABLE statement.
+	 * 
+	 * @param stat
+	 * @param def
+	 */
+	<T> void prepareCreateTable(SQLStatement stat, TableDefinition<T> def);
+	
+	/**
 	 * Get the CREATE INDEX statement.
 	 * 
 	 * @param schemaName
@@ -66,8 +74,8 @@ public interface SQLDialect {
 	 *            the index definition
 	 * @return the SQL statement
 	 */
-	String prepareCreateIndex(String schemaName, String tableName, IndexDefinition index);
-	
+	void prepareCreateIndex(SQLStatement stat, String schemaName, String tableName, IndexDefinition index);
+
 	/**
 	 * Get a MERGE or REPLACE INTO statement.
 	 * 
@@ -78,7 +86,8 @@ public interface SQLDialect {
 	 * @param index
 	 *            the index definition
 	 */
-	<T> void prepareMerge(SQLStatement stat, String schemaName, String tableName, TableDefinition<T> def, Object obj);
+	<T> void prepareMerge(SQLStatement stat, String schemaName, String tableName, TableDefinition<T> def,
+			Object obj);
 
 	/**
 	 * Append "LIMIT limit" to the SQL statement.
@@ -106,18 +115,12 @@ public interface SQLDialect {
 	 * @return true if they are
 	 */
 	boolean supportsMemoryTables();
-
-	/**
-	 * Whether merge is a supported function.
-	 * 
-	 * @return true if they are
-	 */
-	boolean supportsMerge();
-
+	
 	/**
 	 * Whether LIMIT/OFFSET notation is supported.
 	 * 
 	 * @return true if they are
 	 */
 	boolean supportsLimitOffset();
+
 }
diff --git a/src/com/iciql/SQLDialectDefault.java b/src/com/iciql/SQLDialectDefault.java
index 760a1f4..3fd2067 100644
--- a/src/com/iciql/SQLDialectDefault.java
+++ b/src/com/iciql/SQLDialectDefault.java
@@ -20,7 +20,9 @@ package com.iciql;
 import java.sql.DatabaseMetaData;

 import java.sql.SQLException;

 

+import com.iciql.TableDefinition.FieldDefinition;

 import com.iciql.TableDefinition.IndexDefinition;

+import com.iciql.util.StatementBuilder;

 import com.iciql.util.StringUtils;

 

 /**

@@ -49,13 +51,18 @@ public class SQLDialectDefault implements SQLDialect {
 		}

 	}

 

-	@Override

-	public boolean supportsMemoryTables() {

-		return false;

+	/**

+	 * Allows subclasses to change the type of a column for a CREATE statement.

+	 * 

+	 * @param sqlType

+	 * @return the SQL type or a preferred alternative

+	 */

+	protected String convertSqlType(String sqlType) {

+		return sqlType;

 	}

 

 	@Override

-	public boolean supportsMerge() {

+	public boolean supportsMemoryTables() {

 		return false;

 	}

 

@@ -78,12 +85,100 @@ public class SQLDialectDefault implements SQLDialect {
 	}

 

 	@Override

-	public String prepareCreateIndex(String schemaName, String tableName, IndexDefinition index) {

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

+		StatementBuilder buff;

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

+			buff = new StatementBuilder("CREATE MEMORY TABLE IF NOT EXISTS ");

+		} else {

+			buff = new StatementBuilder("CREATE TABLE IF NOT EXISTS ");

+		}

+

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

+

+		boolean hasIdentityColumn = false;

+		for (FieldDefinition field : def.fields) {

+			buff.appendExceptFirst(", ");

+			buff.append(prepareColumnName(field.columnName)).append(' ');

+			String dataType = field.dataType;

+			if (dataType.equals("VARCHAR")) {		

+				// check to see if we should use VARCHAR or CLOB

+				if (field.length <= 0) {

+					dataType = "CLOB";

+				}

+				buff.append(convertSqlType(dataType));

+				if (field.length > 0) {

+					buff.append('(').append(field.length).append(')');

+				}

+			} else if (dataType.equals("DECIMAL")) {

+				// DECIMAL(precision,scale)

+				buff.append(convertSqlType(dataType));				

+				if (field.length > 0) {

+					buff.append('(').append(field.length);

+					if (field.scale > 0) {

+						buff.append(',').append(field.scale);

+					}

+					buff.append(')');

+				}

+			} else {

+				// other

+				buff.append(convertSqlType(dataType));

+			}

+

+			hasIdentityColumn |= prepareColumnDefinition(buff, field.isAutoIncrement, field.isPrimaryKey);

+

+			if (!field.nullable) {

+				buff.append(" NOT NULL");

+			}

+

+			// default values

+			if (!field.isAutoIncrement && !field.isPrimaryKey) {

+				String dv = field.defaultValue;

+				if (!StringUtils.isNullOrEmpty(dv)) {

+					if (ModelUtils.isProperlyFormattedDefaultValue(dv)

+							&& ModelUtils.isValidDefaultValue(field.field.getType(), dv)) {

+						buff.append(" DEFAULT " + dv);

+					}

+				}

+			}

+		}

+

+		// if table does not have identity column then specify primary key

+		if (!hasIdentityColumn) {

+			if (def.primaryKeyColumnNames != null && def.primaryKeyColumnNames.size() > 0) {

+				buff.append(", PRIMARY KEY(");

+				buff.resetCount();

+				for (String n : def.primaryKeyColumnNames) {

+					buff.appendExceptFirst(", ");

+					buff.append(prepareColumnName(n));

+				}

+				buff.append(')');

+			}

+		}

+		buff.append(')');

+		stat.setSQL(buff.toString());

+	}

+

+	protected boolean prepareColumnDefinition(StatementBuilder buff, boolean isAutoIncrement,

+			boolean isPrimaryKey) {

+		boolean isIdentity = false;

+		if (isAutoIncrement && isPrimaryKey) {

+			buff.append(" IDENTITY");

+			isIdentity = true;

+		} else if (isAutoIncrement) {

+			buff.append(" AUTO_INCREMENT");

+		}

+		return isIdentity;

+	}

+

+	@Override

+	public void prepareCreateIndex(SQLStatement stat, String schemaName, String tableName,

+			IndexDefinition index) {

 		throw new IciqlException("Dialect does not support index creation!");

 	}

 

 	@Override

-	public <T> void prepareMerge(SQLStatement stat, String schemaName, String tableName, TableDefinition<T> def, Object obj) {

+	public <T> void prepareMerge(SQLStatement stat, String schemaName, String tableName,

+			TableDefinition<T> def, Object obj) {

 		throw new IciqlException("Dialect does not support merge statements!");

 	}

 

diff --git a/src/com/iciql/SQLDialectH2.java b/src/com/iciql/SQLDialectH2.java
index c65c277..80786f9 100644
--- a/src/com/iciql/SQLDialectH2.java
+++ b/src/com/iciql/SQLDialectH2.java
@@ -31,12 +31,7 @@ public class SQLDialectH2 extends SQLDialectDefault {
 	}

 

 	@Override

-	public boolean supportsMerge() {

-		return true;

-	}

-

-	@Override

-	public String prepareCreateIndex(String schema, String table, IndexDefinition index) {

+	public void prepareCreateIndex(SQLStatement stat, String schema, String table, IndexDefinition index) {

 		StatementBuilder buff = new StatementBuilder();

 		buff.append("CREATE ");

 		switch (index.type) {

@@ -62,7 +57,7 @@ public class SQLDialectH2 extends SQLDialectDefault {
 			buff.append(col);

 		}

 		buff.append(")");

-		return buff.toString();

+		stat.setSQL(buff.toString());		

 	}

 	

 	@Override

diff --git a/src/com/iciql/SQLDialectHSQL.java b/src/com/iciql/SQLDialectHSQL.java
new file mode 100644
index 0000000..e617758
--- /dev/null
+++ b/src/com/iciql/SQLDialectHSQL.java
@@ -0,0 +1,68 @@
+/*

+ * Copyright 2011 James Moger.

+ *

+ * Licensed under the Apache License, Version 2.0 (the "License");

+ * you may not use this file except in compliance with the License.

+ * You may obtain a copy of the License at

+ *

+ *     http://www.apache.org/licenses/LICENSE-2.0

+ *

+ * Unless required by applicable law or agreed to in writing, software

+ * distributed under the License is distributed on an "AS IS" BASIS,

+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+ * See the License for the specific language governing permissions and

+ * limitations under the License.

+ */

+

+package com.iciql;

+

+import com.iciql.TableDefinition.IndexDefinition;

+import com.iciql.util.StatementBuilder;

+

+/**

+ * HyperSQL database dialect.

+ */

+public class SQLDialectHSQL extends SQLDialectDefault {

+		

+	@Override

+	public boolean supportsMemoryTables() {

+		return true;

+	}

+		

+	@Override

+	protected boolean prepareColumnDefinition(StatementBuilder buff, boolean isAutoIncrement, boolean isPrimaryKey) {

+		boolean isIdentity = false;

+		if (isAutoIncrement && isPrimaryKey) {

+			buff.append(" IDENTITY");

+			isIdentity = true;

+		}

+		return isIdentity;

+	}

+

+	@Override

+	public void prepareCreateIndex(SQLStatement stat, String schema, String table, IndexDefinition index) {

+		StatementBuilder buff = new StatementBuilder();

+		buff.append("CREATE ");

+		switch (index.type) {

+		case STANDARD:

+			break;

+		case UNIQUE:

+			buff.append("UNIQUE ");

+			break;

+		case UNIQUE_HASH:

+			buff.append("UNIQUE ");

+			break;

+		}

+		buff.append("INDEX ");

+		buff.append(index.indexName);

+		buff.append(" ON ");

+		buff.append(table);

+		buff.append("(");

+		for (String col : index.columnNames) {

+			buff.appendExceptFirst(", ");

+			buff.append(col);

+		}

+		buff.append(")");

+		stat.setSQL(buff.toString());

+	}

+}
\ No newline at end of file
diff --git a/src/com/iciql/SQLDialectMySQL.java b/src/com/iciql/SQLDialectMySQL.java
index 837d77b..2593e0a 100644
--- a/src/com/iciql/SQLDialectMySQL.java
+++ b/src/com/iciql/SQLDialectMySQL.java
@@ -16,7 +16,6 @@
 

 package com.iciql;

 

-import com.iciql.TableDefinition.FieldDefinition;

 import com.iciql.TableDefinition.IndexDefinition;

 import com.iciql.util.StatementBuilder;

 

@@ -26,13 +25,16 @@ import com.iciql.util.StatementBuilder;
 public class SQLDialectMySQL extends SQLDialectDefault {

 

 	@Override

-	public boolean supportsMemoryTables() {

-		return false;

+	protected String convertSqlType(String sqlType) {

+		if (sqlType.equals("CLOB")) {

+			return "TEXT";

+		}

+		return sqlType;

 	}

-

+	

 	@Override

-	public boolean supportsMerge() {

-		return true;

+	public boolean supportsMemoryTables() {

+		return false;

 	}

 

 	@Override

@@ -41,7 +43,15 @@ public class SQLDialectMySQL extends SQLDialectDefault {
 	}

 	

 	@Override

-	public String prepareCreateIndex(String schema, String table, IndexDefinition index) {

+	protected boolean prepareColumnDefinition(StatementBuilder buff, boolean isAutoIncrement, boolean isPrimaryKey) {

+		if (isAutoIncrement) {

+			buff.append(" AUTO_INCREMENT");

+		}

+		return false;

+	}

+	

+	@Override

+	public void prepareCreateIndex(SQLStatement stat, String schema, String table, IndexDefinition index) {

 		StatementBuilder buff = new StatementBuilder();

 		buff.append("CREATE ");

 		switch (index.type) {

@@ -74,26 +84,6 @@ public class SQLDialectMySQL extends SQLDialectDefault {
 			buff.append("USING HASH");

 			break;

 		}

-		return buff.toString().trim();

-	}

-	

-	@Override

-	public <T> void prepareMerge(SQLStatement stat, String schemaName, String tableName, TableDefinition<T> def, Object obj) {		

-		StatementBuilder buff = new StatementBuilder("REPLACE INTO ");

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

-		for (FieldDefinition field : def.fields) {

-			buff.appendExceptFirst(", ");

-			buff.append(prepareColumnName(field.columnName));

-		}

-		buff.append(") VALUES(");

-		buff.resetCount();

-		for (FieldDefinition field : def.fields) {

-			buff.appendExceptFirst(", ");

-			buff.append('?');

-			Object value = def.getValue(obj, field);

-			stat.addParameter(value);

-		}

-		buff.append(')');

-		stat.setSQL(buff.toString());

+		stat.setSQL(buff.toString().trim());

 	}

 }
\ No newline at end of file
diff --git a/src/com/iciql/SQLStatement.java b/src/com/iciql/SQLStatement.java
index 49c7627..4962502 100644
--- a/src/com/iciql/SQLStatement.java
+++ b/src/com/iciql/SQLStatement.java
@@ -52,7 +52,7 @@ public class SQLStatement {
 	public SQLStatement appendTable(String schema, String table) {

 		return appendSQL(db.getDialect().prepareTableName(schema, table));

 	}

-	

+

 	public SQLStatement appendColumn(String column) {

 		return appendSQL(db.getDialect().prepareColumnName(column));

 	}

@@ -63,7 +63,7 @@ public class SQLStatement {
 		}

 		return sql;

 	}

-	

+

 	public SQLStatement addParameter(Object o) {

 		params.add(o);

 		return this;

@@ -73,7 +73,7 @@ public class SQLStatement {
 		try {

 			return prepare(false).executeQuery();

 		} catch (SQLException e) {

-			throw new IciqlException(e);

+			throw IciqlException.fromSQL(getSQL(), e);

 		}

 	}

 

@@ -83,7 +83,7 @@ public class SQLStatement {
 			ps = prepare(false);

 			return ps.executeUpdate();

 		} catch (SQLException e) {

-			throw new IciqlException(e);

+			throw IciqlException.fromSQL(getSQL(), e);

 		} finally {

 			JdbcUtils.closeSilently(ps);

 		}

@@ -102,17 +102,19 @@ public class SQLStatement {
 			JdbcUtils.closeSilently(rs);

 			return identity;

 		} catch (SQLException e) {

-			throw new IciqlException(e);

+			throw IciqlException.fromSQL(getSQL(), e);

 		} finally {

 			JdbcUtils.closeSilently(ps);

 		}

 	}

 

-	private static void setValue(PreparedStatement prep, int parameterIndex, Object x) {

+	private void setValue(PreparedStatement prep, int parameterIndex, Object x) {

 		try {

 			prep.setObject(parameterIndex, x);

 		} catch (SQLException e) {

-			throw new IciqlException(e);

+			IciqlException ix = new IciqlException(e, "error setting parameter {0} as {1}", parameterIndex, x.getClass().getSimpleName());

+			ix.setSQL(getSQL());

+			throw ix;

 		}

 	}

 

diff --git a/src/com/iciql/TableDefinition.java b/src/com/iciql/TableDefinition.java
index c08a032..571ab1f 100644
--- a/src/com/iciql/TableDefinition.java
+++ b/src/com/iciql/TableDefinition.java
@@ -71,10 +71,11 @@ public class TableDefinition<T> {
 		String columnName;

 		Field field;

 		String dataType;

-		int maxLength;

+		int length;

+		int scale;

 		boolean isPrimaryKey;

 		boolean isAutoIncrement;

-		boolean trimString;

+		boolean trim;

 		boolean nullable;

 		String defaultValue;

 		EnumType enumType;

@@ -125,13 +126,14 @@ public class TableDefinition<T> {
 	String schemaName;

 	String tableName;

 	int tableVersion;

-	private boolean createTableIfRequired = true;

+	List<String> primaryKeyColumnNames;

+	boolean memoryTable;

+	

+	private boolean createTableIfRequired = true;	

 	private Class<T> clazz;

 	private IdentityHashMap<Object, FieldDefinition> fieldMap = Utils.newIdentityHashMap();

-

-	private List<String> primaryKeyColumnNames;

 	private ArrayList<IndexDefinition> indexes = Utils.newArrayList();

-	private boolean memoryTable;

+	

 

 	TableDefinition(Class<T> clazz) {

 		this.clazz = clazz;

@@ -242,10 +244,17 @@ public class TableDefinition<T> {
 		}

 	}

 

-	public void setMaxLength(Object column, int maxLength) {

+	public void setLength(Object column, int length) {

+		FieldDefinition def = fieldMap.get(column);

+		if (def != null) {

+			def.length = length;

+		}

+	}

+	

+	public void setScale(Object column, int scale) {

 		FieldDefinition def = fieldMap.get(column);

 		if (def != null) {

-			def.maxLength = maxLength;

+			def.scale = scale;

 		}

 	}

 

@@ -273,8 +282,9 @@ public class TableDefinition<T> {
 			String columnName = f.getName();

 			boolean isAutoIncrement = false;

 			boolean isPrimaryKey = false;

-			int maxLength = 0;

-			boolean trimString = false;

+			int length = 0;

+			int scale = 0;

+			boolean trim = false;

 			boolean nullable = true;

 			EnumType enumType = null;

 			String defaultValue = "";

@@ -301,8 +311,9 @@ public class TableDefinition<T> {
 				}

 				isAutoIncrement = col.autoIncrement();

 				isPrimaryKey = col.primaryKey();

-				maxLength = col.length();

-				trimString = col.trim();

+				length = col.length();

+				scale = col.scale();

+				trim = col.trim();

 				nullable = col.nullable();

 

 				// try using default object

@@ -321,7 +332,7 @@ public class TableDefinition<T> {
 						}

 					}

 				} catch (IllegalAccessException e) {

-					throw new IciqlException(e, "Failed to get default object for {0}", columnName);

+					throw new IciqlException(e, "failed to get default object for {0}", columnName);

 				}

 

 				// annotation overrides

@@ -338,8 +349,9 @@ public class TableDefinition<T> {
 				fieldDef.columnName = columnName;

 				fieldDef.isAutoIncrement = isAutoIncrement;

 				fieldDef.isPrimaryKey = isPrimaryKey;

-				fieldDef.maxLength = maxLength;

-				fieldDef.trimString = trimString;

+				fieldDef.length = length;

+				fieldDef.scale = scale;

+				fieldDef.trim = trim;

 				fieldDef.nullable = nullable;

 				fieldDef.defaultValue = defaultValue;

 				fieldDef.enumType = enumType;

@@ -372,9 +384,9 @@ public class TableDefinition<T> {
 			Enum<?> iqenum = (Enum<?>) value;

 			switch (field.enumType) {

 			case NAME:

-				if (field.trimString && field.maxLength > 0) {

-					if (iqenum.name().length() > field.maxLength) {

-						return iqenum.name().substring(0, field.maxLength);

+				if (field.trim && field.length > 0) {

+					if (iqenum.name().length() > field.length) {

+						return iqenum.name().substring(0, field.length);

 					}

 				}

 				return iqenum.name();

@@ -388,12 +400,13 @@ public class TableDefinition<T> {
 				return enumid.enumId();

 			}

 		}

-		if (field.trimString && field.maxLength > 0) {

+		

+		if (field.trim && field.length > 0) {

 			if (value instanceof String) {

 				// clip strings

 				String s = (String) value;

-				if (s.length() > field.maxLength) {

-					return s.substring(0, field.maxLength);

+				if (s.length() > field.length) {

+					return s.substring(0, field.length);

 				}

 				return s;

 			}

@@ -514,65 +527,23 @@ public class TableDefinition<T> {
 			db.upgradeTable(this);

 			return this;

 		}

-		SQLDialect dialect = db.getDialect();

 		SQLStatement stat = new SQLStatement(db);

-		StatementBuilder buff;

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

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

-		} else {

-			buff = new StatementBuilder("CREATE TABLE IF NOT EXISTS ");

-		}

-

-		buff.append(dialect.prepareTableName(schemaName, tableName)).append('(');

-

-		for (FieldDefinition field : fields) {

-			buff.appendExceptFirst(", ");

-			buff.append(dialect.prepareColumnName(field.columnName)).append(' ').append(field.dataType);

-			if (field.maxLength > 0) {

-				buff.append('(').append(field.maxLength).append(')');

-			}

-

-			if (field.isAutoIncrement) {

-				buff.append(" AUTO_INCREMENT");

-			}

-

-			if (!field.nullable) {

-				buff.append(" NOT NULL");

-			}

-

-			// default values

-			if (!field.isAutoIncrement && !field.isPrimaryKey) {

-				String dv = field.defaultValue;

-				if (!StringUtils.isNullOrEmpty(dv)) {

-					if (ModelUtils.isProperlyFormattedDefaultValue(dv)

-							&& ModelUtils.isValidDefaultValue(field.field.getType(), dv)) {

-						buff.append(" DEFAULT " + dv);

-					}

-				}

-			}

-		}

-

-		// primary key

-		if (primaryKeyColumnNames != null && primaryKeyColumnNames.size() > 0) {

-			buff.append(", PRIMARY KEY(");

-			buff.resetCount();

-			for (String n : primaryKeyColumnNames) {

-				buff.appendExceptFirst(", ");

-				buff.append(n);

-			}

-			buff.append(')');

-		}

-		buff.append(')');

-		stat.setSQL(buff.toString());

+		db.getDialect().prepareCreateTable(stat, this);		

 		StatementLogger.create(stat.getSQL());

 		stat.executeUpdate();

 

 		// create indexes

 		for (IndexDefinition index : indexes) {

-			String sql = db.getDialect().prepareCreateIndex(schemaName, tableName, index);

-			stat.setSQL(sql);

+			stat = new SQLStatement(db);

+			db.getDialect().prepareCreateIndex(stat, schemaName, tableName, index);			

 			StatementLogger.create(stat.getSQL());

-			stat.executeUpdate();

+			try {

+				stat.executeUpdate();

+			} catch (IciqlException e) {

+				if (e.getIciqlCode() != IciqlException.CODE_INDEX_ALREADY_EXISTS) {

+					throw e;

+				}

+			}

 		}

 

 		// tables are created using IF NOT EXISTS

diff --git a/src/com/iciql/TableInspector.java b/src/com/iciql/TableInspector.java
index a5b6dee..e682f2f 100644
--- a/src/com/iciql/TableInspector.java
+++ b/src/com/iciql/TableInspector.java
@@ -118,9 +118,12 @@ public class TableInspector {
 			indexes = Utils.newHashMap();
 			while (rs.next()) {
 				IndexInspector info = new IndexInspector(rs);
-				if (info.type.equals(IndexType.UNIQUE) && info.name.toLowerCase().startsWith("primary")) {
-					// skip primary key indexes
-					continue;
+				if (info.type.equals(IndexType.UNIQUE)) {
+					String name = info.name.toLowerCase();
+					if (name.startsWith("primary") || name.startsWith("sys_idx_sys_pk")) {
+						// skip primary key indexes
+						continue;
+					}
 				}
 				if (indexes.containsKey(info.name)) {
 					indexes.get(info.name).addColumn(rs);
@@ -140,7 +143,20 @@ public class TableInspector {
 				col.clazz = ModelUtils.getClassForSqlType(col.type, dateTimeClass);
 				col.size = rs.getInt("COLUMN_SIZE");
 				col.nullable = rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable;
-				col.isAutoIncrement = rs.getBoolean("IS_AUTOINCREMENT");
+				try {
+					Object autoIncrement = rs.getObject("IS_AUTOINCREMENT");
+					if (autoIncrement instanceof Boolean) {
+						col.isAutoIncrement = (Boolean) autoIncrement;
+					} else if (autoIncrement instanceof String) {
+						String val = autoIncrement.toString().toLowerCase();
+						col.isAutoIncrement = val.equals("true") | val.equals("yes");
+					} else if (autoIncrement instanceof Number) {
+						Number n = (Number) autoIncrement;
+						col.isAutoIncrement = n.intValue() > 0;
+					}
+				} catch (SQLException s) {
+					throw s;
+				}
 				if (primaryKeys.size() == 1) {
 					if (col.name.equalsIgnoreCase(primaryKeys.get(0))) {
 						col.isPrimaryKey = true;
@@ -499,14 +515,14 @@ public class TableInspector {
 
 		// string types
 		if (fieldClass == String.class) {
-			if ((fieldDef.maxLength != col.size) && (col.size < Integer.MAX_VALUE)) {
+			if ((fieldDef.length != col.size) && (col.size < Integer.MAX_VALUE)) {
 				remarks.add(warn(
 						table,
 						col,
 						format("{0}.length={1}, ColumnMaxLength={2}", IQColumn.class.getSimpleName(),
-								fieldDef.maxLength, col.size)));
+								fieldDef.length, col.size)));
 			}
-			if (fieldDef.maxLength > 0 && !fieldDef.trimString) {
+			if (fieldDef.length > 0 && !fieldDef.trim) {
 				remarks.add(consider(table, col, format("{0}.trim=true will prevent IciqlExceptions on"
 						+ " INSERT or UPDATE, but will clip data!", IQColumn.class.getSimpleName())));
 			}
@@ -687,7 +703,7 @@ public class TableInspector {
 			}
 		}
 
-		void addEnum(String parameter, Enum value) {
+		void addEnum(String parameter, Enum<?> value) {
 			appendExceptFirst(", ");
 			if (!StringUtils.isNullOrEmpty(parameter)) {
 				append(parameter);
diff --git a/src/com/iciql/ValidationRemark.java b/src/com/iciql/ValidationRemark.java
index a68bf21..33320ab 100644
--- a/src/com/iciql/ValidationRemark.java
+++ b/src/com/iciql/ValidationRemark.java
@@ -35,11 +35,11 @@ public class ValidationRemark {
 		CONSIDER, WARN, ERROR;
 	}
 
-	private Level level;
-	private String table;
-	private String fieldType;
-	private String fieldName;
-	private String message;
+	public final Level level;
+	public final String table;
+	public final String fieldType;
+	public final String fieldName;
+	public final String message;
 
 	private ValidationRemark(Level level, String table, String type, String message) {
 		this.level = level;
@@ -104,10 +104,6 @@ public class ValidationRemark {
 		return level.equals(Level.ERROR);
 	}
 
-	public Level getLevel() {
-		return level;
-	}
-
 	public String toString() {
 		StringBuilder sb = new StringBuilder();
 		sb.append(StringUtils.pad(level.name(), 9, " ", true));
diff --git a/src/com/iciql/build/Build.java b/src/com/iciql/build/Build.java
index 5963d16..cc3895c 100644
--- a/src/com/iciql/build/Build.java
+++ b/src/com/iciql/build/Build.java
@@ -56,6 +56,7 @@ public class Build {
 	public static void compiletime() {

 		downloadFromApache(MavenObject.H2, BuildType.RUNTIME);

 		downloadFromApache(MavenObject.H2, BuildType.COMPILETIME);

+		downloadFromApache(MavenObject.HSQLDB, BuildType.RUNTIME);

 		downloadFromApache(MavenObject.JCOMMANDER, BuildType.RUNTIME);

 		downloadFromApache(MavenObject.JCOMMANDER, BuildType.COMPILETIME);

 		downloadFromApache(MavenObject.MARKDOWNPAPERS, BuildType.RUNTIME);

@@ -172,6 +173,10 @@ public class Build {
 				"4bac13427caeb32ef6e93b70101e61f370c7b5e2", "6bb165156a0831879fa7797df6e18bdcd4421f2d",

 				"446d3f58c44992534cb54f67134532d95961904a");

 

+		public static final MavenObject HSQLDB = new MavenObject("org/hsqldb", "hsqldb", "2.2.4",

+				"6a6e040b07f5ee409fc825f1c5e5b574b1fa1428", "",

+				"");

+

 		public static final MavenObject JUNIT = new MavenObject("junit", "junit", "4.8.2",

 				"c94f54227b08100974c36170dcb53329435fe5ad", "", "");

 

diff --git a/src/com/iciql/util/Utils.java b/src/com/iciql/util/Utils.java
index fa794cf..6f9746d 100644
--- a/src/com/iciql/util/Utils.java
+++ b/src/com/iciql/util/Utils.java
@@ -116,7 +116,7 @@ public class Utils {
 							}

 						}

 					}

-					throw new IciqlException("Missing default constructor?  Exception trying to create " + clazz.getName() + ": " + e, e);

+					throw new IciqlException(e, "Missing default constructor?  Exception trying to instantiate {0}: {1}", clazz.getName(), e.getMessage());

 				}

 			}

 		};

@@ -190,7 +190,7 @@ public class Utils {
 					}

 				}

 			}

-			throw new IciqlException("Exception trying to create " + clazz.getName() + ": " + e, e);

+			throw new IciqlException(e, "Missing default constructor?! Exception trying to instantiate {0}: {1}", clazz.getName(), e.getMessage());

 		}

 	}

 

@@ -220,18 +220,28 @@ public class Utils {
 					Reader r = c.getCharacterStream();

 					return readStringAndClose(r, -1);

 				} catch (Exception e) {

-					throw new IciqlException("Error converting CLOB to String: " + e.toString(), e);

+					throw new IciqlException(e, "error converting CLOB to String: ", e.toString());

 				}

 			}

 			return o.toString();

 		}

 

-		// convert from number to boolean

 		if (Boolean.class.isAssignableFrom(targetType) || boolean.class.isAssignableFrom(targetType)) {

+			// convert from number to boolean

 			if (Number.class.isAssignableFrom(currentType)) {

 				Number n = (Number) o;

 				return n.intValue() > 0;

 			}

+			// convert from string to boolean

+			if (String.class.isAssignableFrom(currentType)) {

+				String s = o.toString().toLowerCase();

+				float f = 0f;

+				try {

+					f = Float.parseFloat(s);

+				} catch (Exception e) {					

+				}

+				return f > 0 || s.equals("true") || s.equals("yes");

+			}

 		}

 

 		// convert from boolean to number

@@ -271,7 +281,7 @@ public class Utils {
 					InputStream is = b.getBinaryStream();

 					return readBlobAndClose(is, -1);

 				} catch (Exception e) {

-					throw new IciqlException("Error converting BLOB to byte[]: " + e.toString(), e);

+					throw new IciqlException(e, "error converting BLOB to byte[]: ", e.toString());

 				}

 			}

 		}

@@ -315,7 +325,7 @@ public class Utils {
 				Reader r = c.getCharacterStream();

 				name = readStringAndClose(r, -1);

 			} catch (Exception e) {

-				throw new IciqlException("Error converting CLOB to String: " + e.toString(), e);

+				throw new IciqlException(e, "error converting CLOB to String: ", e.toString());

 			}

 

 			// find name match