From 1734dc44d70391aeef26cacdf4d9f07c289e476e Mon Sep 17 00:00:00 2001 From: James Moger Date: Wed, 17 Aug 2011 08:02:23 -0400 Subject: [PATCH] Added support for PostgreSQL. Passes all but the boolean-as-int tests. --- .classpath | 1 + src/com/iciql/Db.java | 8 +- src/com/iciql/DbInspector.java | 3 +- src/com/iciql/IciqlException.java | 45 +++---- src/com/iciql/ModelUtils.java | 3 + src/com/iciql/SQLDialectDefault.java | 89 ++++++++++++-- src/com/iciql/SQLDialectDerby.java | 85 +------------- src/com/iciql/SQLDialectH2.java | 21 ++++ src/com/iciql/SQLDialectHSQL.java | 32 +---- src/com/iciql/SQLDialectMySQL.java | 39 +------ src/com/iciql/SQLDialectPostgreSQL.java | 110 ++++++++++++++++++ src/com/iciql/TableDefinition.java | 4 +- src/com/iciql/TableInspector.java | 14 +-- src/com/iciql/build/Build.java | 4 + src/com/iciql/util/Utils.java | 2 +- tests/com/iciql/test/AnnotationsTest.java | 43 +++---- tests/com/iciql/test/IciqlSuite.java | 10 +- tests/com/iciql/test/ModelsTest.java | 15 ++- .../test/models/ProductAnnotationOnly.java | 4 +- 19 files changed, 316 insertions(+), 216 deletions(-) create mode 100644 src/com/iciql/SQLDialectPostgreSQL.java diff --git a/.classpath b/.classpath index 136c201..a842b70 100644 --- a/.classpath +++ b/.classpath @@ -32,5 +32,6 @@ + diff --git a/src/com/iciql/Db.java b/src/com/iciql/Db.java index d369c2c..da0a7ac 100644 --- a/src/com/iciql/Db.java +++ b/src/com/iciql/Db.java @@ -72,10 +72,11 @@ public class Db { // can register by... // 1. Connection class name // 2. DatabaseMetaData.getDatabaseProductName() + DIALECTS.put("Apache Derby", SQLDialectDerby.class); DIALECTS.put("H2", SQLDialectH2.class); - DIALECTS.put("MySQL", SQLDialectMySQL.class); DIALECTS.put("HSQL Database Engine", SQLDialectHSQL.class); - DIALECTS.put("Apache Derby", SQLDialectDerby.class); + DIALECTS.put("MySQL", SQLDialectMySQL.class); + DIALECTS.put("PostgreSQL", SQLDialectPostgreSQL.class); } private Db(Connection conn) { @@ -249,8 +250,7 @@ public class Db { try { rc = stat.executeUpdate(); } catch (IciqlException e) { - if (e.getIciqlCode() != IciqlException.CODE_SCHEMA_NOT_FOUND - && e.getIciqlCode() != IciqlException.CODE_TABLE_NOT_FOUND) { + if (e.getIciqlCode() != IciqlException.CODE_OBJECT_NOT_FOUND) { throw e; } } diff --git a/src/com/iciql/DbInspector.java b/src/com/iciql/DbInspector.java index c30bbb1..12124b8 100644 --- a/src/com/iciql/DbInspector.java +++ b/src/com/iciql/DbInspector.java @@ -169,8 +169,7 @@ public class DbInspector { while (rs.next()) { String t = rs.getString("TABLE_NAME"); if (!t.equalsIgnoreCase(iciqlTables)) { - tables.add(new TableInspector(s, t, getMetaData().storesUpperCaseIdentifiers(), - dateTimeClass)); + tables.add(new TableInspector(s, t, dateTimeClass)); } } } diff --git a/src/com/iciql/IciqlException.java b/src/com/iciql/IciqlException.java index 524d184..4670418 100644 --- a/src/com/iciql/IciqlException.java +++ b/src/com/iciql/IciqlException.java @@ -27,10 +27,8 @@ 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_SCHEMA_NOT_FOUND = 3; - public static final int CODE_TABLE_NOT_FOUND = 4; - public static final int CODE_TABLE_ALREADY_EXISTS = 5; - public static final int CODE_INDEX_ALREADY_EXISTS = 6; + public static final int CODE_OBJECT_NOT_FOUND = 3; + public static final int CODE_OBJECT_ALREADY_EXISTS = 4; private static final String TOKEN_UNMAPPED_FIELD = "\\? (=|\\>|\\<|\\<\\>|!=|\\>=|\\<=|LIKE|BETWEEN) \\?"; @@ -97,34 +95,41 @@ public class IciqlException extends RuntimeException { SQLException s = (SQLException) t; String state = s.getSQLState(); if ("23000".equals(state)) { - // MySQL + // MySQL duplicate primary key on insert iciqlCode = CODE_DUPLICATE_KEY; } else if ("23505".equals(state)) { + // Derby duplicate primary key on insert iciqlCode = CODE_DUPLICATE_KEY; } else if ("42000".equals(state)) { - // MySQL + // MySQL duplicate unique index value on insert iciqlCode = CODE_DUPLICATE_KEY; } else if ("42Y07".equals(state)) { - // Derby - iciqlCode = CODE_SCHEMA_NOT_FOUND; + // Derby schema not found + iciqlCode = CODE_OBJECT_NOT_FOUND; } else if ("42X05".equals(state)) { - // Derby - iciqlCode = CODE_TABLE_NOT_FOUND; + // Derby table not found + iciqlCode = CODE_OBJECT_NOT_FOUND; } else if ("42S02".equals(state)) { - // H2 - iciqlCode = CODE_TABLE_NOT_FOUND; + // H2 table not found + iciqlCode = CODE_OBJECT_NOT_FOUND; } else if ("42501".equals(state)) { - // HSQL - iciqlCode = CODE_TABLE_NOT_FOUND; + // HSQL table not found + iciqlCode = CODE_OBJECT_NOT_FOUND; + } else if ("42P01".equals(state)) { + // PostgreSQL table not found + iciqlCode = CODE_OBJECT_NOT_FOUND; } else if ("X0Y32".equals(state)) { - // Derby - iciqlCode = CODE_TABLE_ALREADY_EXISTS; + // Derby table already exists + iciqlCode = CODE_OBJECT_ALREADY_EXISTS; + } else if ("42P07".equals(state)) { + // PostgreSQL table or index already exists + iciqlCode = CODE_OBJECT_ALREADY_EXISTS; } else if ("42S11".equals(state)) { - // H2 - iciqlCode = CODE_INDEX_ALREADY_EXISTS; + // H2 index already exists + iciqlCode = CODE_OBJECT_ALREADY_EXISTS; } else if ("42504".equals(state)) { - // HSQL - iciqlCode = CODE_INDEX_ALREADY_EXISTS; + // HSQL index already exists + iciqlCode = CODE_OBJECT_ALREADY_EXISTS; } } } diff --git a/src/com/iciql/ModelUtils.java b/src/com/iciql/ModelUtils.java index 5613d00..522da2a 100644 --- a/src/com/iciql/ModelUtils.java +++ b/src/com/iciql/ModelUtils.java @@ -113,6 +113,8 @@ class ModelUtils { m.put("SIGNED", "INT"); m.put("INT8", "BIGINT"); m.put("IDENTITY", "BIGINT"); + m.put("SERIAL", "INT"); + m.put("BIGSERIAL", "BIGINT"); // decimal m.put("NUMBER", "DECIMAL"); @@ -121,6 +123,7 @@ class ModelUtils { m.put("FLOAT", "DOUBLE"); m.put("FLOAT4", "DOUBLE"); m.put("FLOAT8", "DOUBLE"); + m.put("DOUBLE PRECISION", "DOUBLE"); // date m.put("DATETIME", "TIMESTAMP"); diff --git a/src/com/iciql/SQLDialectDefault.java b/src/com/iciql/SQLDialectDefault.java index 0b611e0..7ef507a 100644 --- a/src/com/iciql/SQLDialectDefault.java +++ b/src/com/iciql/SQLDialectDefault.java @@ -19,6 +19,7 @@ package com.iciql; import java.sql.DatabaseMetaData; import java.sql.SQLException; +import java.text.MessageFormat; import com.iciql.TableDefinition.FieldDefinition; import com.iciql.TableDefinition.IndexDefinition; @@ -26,8 +27,7 @@ import com.iciql.util.StatementBuilder; import com.iciql.util.StringUtils; /** - * Default implementation of an SQL dialect. Does not support merge nor index - * creation. + * Default implementation of an SQL dialect. */ public class SQLDialectDefault implements SQLDialect { float databaseVersion; @@ -144,11 +144,10 @@ public class SQLDialectDefault implements SQLDialect { } } else { // other - buff.append(convertSqlType(dataType)); + hasIdentityColumn |= prepareColumnDefinition(buff, convertSqlType(dataType), + field.isAutoIncrement, field.isPrimaryKey); } - hasIdentityColumn |= prepareColumnDefinition(buff, field.isAutoIncrement, field.isPrimaryKey); - if (!field.nullable) { buff.append(" NOT NULL"); } @@ -181,8 +180,21 @@ public class SQLDialectDefault implements SQLDialect { stat.setSQL(buff.toString()); } - protected boolean prepareColumnDefinition(StatementBuilder buff, boolean isAutoIncrement, - boolean isPrimaryKey) { + protected boolean isIntegerType(String dataType) { + if ("INT".equals(dataType)) { + return true; + } else if ("BIGINT".equals(dataType)) { + return true; + } else if ("TINYINT".equals(dataType)) { + return true; + } else if ("SMALLINT".equals(dataType)) { + return true; + } + return false; + } + + protected boolean prepareColumnDefinition(StatementBuilder buff, String dataType, + boolean isAutoIncrement, boolean isPrimaryKey) { boolean isIdentity = false; if (isAutoIncrement && isPrimaryKey) { buff.append(" IDENTITY"); @@ -196,13 +208,72 @@ public class SQLDialectDefault implements SQLDialect { @Override public void prepareCreateIndex(SQLStatement stat, String schemaName, String tableName, IndexDefinition index) { - throw new IciqlException("{0} does not support index creation!", getClass().getSimpleName()); + StatementBuilder buff = new StatementBuilder(); + buff.append("CREATE "); + switch (index.type) { + 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(tableName); + buff.append("("); + for (String col : index.columnNames) { + buff.appendExceptFirst(", "); + buff.append(prepareColumnName(col)); + } + buff.append(") "); + + stat.setSQL(buff.toString().trim()); } + /** + * PostgreSQL and Derby do not support the SQL2003 MERGE syntax, but we can + * use a trick to insert a row if it does not exist and call update() in + * Db.merge() if the affected row count is 0. + *

+ * Databases that do support a MERGE syntax should override this method. + *

+ * http://stackoverflow.com/questions/407688 + */ @Override public void prepareMerge(SQLStatement stat, String schemaName, String tableName, TableDefinition def, Object obj) { - throw new IciqlException("{0} does not support merge statements!", getClass().getSimpleName()); + StatementBuilder buff = new StatementBuilder("INSERT INTO "); + buff.append(prepareTableName(schemaName, tableName)); + buff.append(" ("); + buff.resetCount(); + for (FieldDefinition field : def.fields) { + buff.appendExceptFirst(", "); + buff.append(prepareColumnName(field.columnName)); + } + buff.append(") (SELECT "); + buff.resetCount(); + for (FieldDefinition field : def.fields) { + buff.appendExceptFirst(", "); + buff.append('?'); + Object value = def.getValue(obj, field); + stat.addParameter(value); + } + buff.append(" FROM "); + buff.append(prepareTableName(schemaName, tableName)); + buff.append(" WHERE "); + buff.resetCount(); + for (FieldDefinition field : def.fields) { + if (field.isPrimaryKey) { + buff.appendExceptFirst(" AND "); + buff.append(MessageFormat.format("{0} = ?", prepareColumnName(field.columnName))); + Object value = def.getValue(obj, field); + stat.addParameter(value); + } + } + buff.append(" HAVING count(*)=0)"); + stat.setSQL(buff.toString()); } @Override diff --git a/src/com/iciql/SQLDialectDerby.java b/src/com/iciql/SQLDialectDerby.java index 019c285..fd06844 100644 --- a/src/com/iciql/SQLDialectDerby.java +++ b/src/com/iciql/SQLDialectDerby.java @@ -16,10 +16,6 @@ package com.iciql; -import java.text.MessageFormat; - -import com.iciql.TableDefinition.FieldDefinition; -import com.iciql.TableDefinition.IndexDefinition; import com.iciql.util.StatementBuilder; /** @@ -68,14 +64,11 @@ public class SQLDialectDerby extends SQLDialectDefault { } @Override - public String prepareColumnName(String name) { - return name; - } - - @Override - protected boolean prepareColumnDefinition(StatementBuilder buff, boolean isAutoIncrement, - boolean isPrimaryKey) { - if (isAutoIncrement) { + protected boolean prepareColumnDefinition(StatementBuilder buff, String dataType, + boolean isAutoIncrement, boolean isPrimaryKey) { + String convertedType = convertSqlType(dataType); + buff.append(convertedType); + if (isIntegerType(dataType) && isAutoIncrement) { buff.append(" GENERATED BY DEFAULT AS IDENTITY"); } return false; @@ -88,72 +81,4 @@ public class SQLDialectDerby extends SQLDialectDefault { stat.setSQL(buff.toString()); return; } - - @Override - public void prepareCreateIndex(SQLStatement stat, String schema, String table, IndexDefinition index) { - StatementBuilder buff = new StatementBuilder(); - buff.append("CREATE "); - switch (index.type) { - 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(prepareColumnName(col)); - } - buff.append(") "); - - stat.setSQL(buff.toString().trim()); - } - - /** - * Derby does not support the SQL2003 MERGE syntax, but we can use a trick - * to insert a row if it does not exist and call update() in Db.merge() if - * the affected row count is 0; - * - * http://stackoverflow.com/questions/407688 - */ - @Override - public void prepareMerge(SQLStatement stat, String schemaName, String tableName, - TableDefinition def, Object obj) { - StatementBuilder buff = new StatementBuilder("INSERT INTO "); - buff.append(prepareTableName(schemaName, tableName)); - buff.append(" ("); - buff.resetCount(); - for (FieldDefinition field : def.fields) { - buff.appendExceptFirst(", "); - buff.append(prepareColumnName(field.columnName)); - } - buff.append(") (SELECT "); - buff.resetCount(); - for (FieldDefinition field : def.fields) { - buff.appendExceptFirst(", "); - buff.append('?'); - Object value = def.getValue(obj, field); - stat.addParameter(value); - } - buff.append(" FROM "); - buff.append(prepareTableName(schemaName, tableName)); - buff.append(" WHERE "); - buff.resetCount(); - for (FieldDefinition field : def.fields) { - if (field.isPrimaryKey) { - buff.appendExceptFirst(" AND "); - buff.append(MessageFormat.format("{0} = ?", prepareColumnName(field.columnName))); - Object value = def.getValue(obj, field); - stat.addParameter(value); - } - } - buff.append(" HAVING count(*)=0)"); - stat.setSQL(buff.toString()); - } } \ No newline at end of file diff --git a/src/com/iciql/SQLDialectH2.java b/src/com/iciql/SQLDialectH2.java index ab08475..6f74b45 100644 --- a/src/com/iciql/SQLDialectH2.java +++ b/src/com/iciql/SQLDialectH2.java @@ -30,6 +30,27 @@ public class SQLDialectH2 extends SQLDialectDefault { return true; } + @Override + 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; + } else if (isAutoIncrement) { + buff.append(convertedType); + buff.append(" AUTO_INCREMENT"); + } else { + buff.append(convertedType); + } + } else { + buff.append(convertedType); + } + return isIdentity; + } + @Override public void prepareCreateIndex(SQLStatement stat, String schema, String table, IndexDefinition index) { StatementBuilder buff = new StatementBuilder(); diff --git a/src/com/iciql/SQLDialectHSQL.java b/src/com/iciql/SQLDialectHSQL.java index 566bb23..3d72ac2 100644 --- a/src/com/iciql/SQLDialectHSQL.java +++ b/src/com/iciql/SQLDialectHSQL.java @@ -19,7 +19,6 @@ package com.iciql; import java.text.MessageFormat; import com.iciql.TableDefinition.FieldDefinition; -import com.iciql.TableDefinition.IndexDefinition; import com.iciql.util.StatementBuilder; /** @@ -33,41 +32,18 @@ public class SQLDialectHSQL extends SQLDialectDefault { } @Override - protected boolean prepareColumnDefinition(StatementBuilder buff, boolean isAutoIncrement, + protected boolean prepareColumnDefinition(StatementBuilder buff, String dataType, boolean isAutoIncrement, boolean isPrimaryKey) { boolean isIdentity = false; - if (isAutoIncrement && isPrimaryKey) { + String convertedType = convertSqlType(dataType); + buff.append(convertedType); + if (isIntegerType(dataType) && 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 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()); - } - @Override public void prepareMerge(SQLStatement stat, String schemaName, String tableName, TableDefinition def, Object obj) { diff --git a/src/com/iciql/SQLDialectMySQL.java b/src/com/iciql/SQLDialectMySQL.java index 1c1674a..0b2acf4 100644 --- a/src/com/iciql/SQLDialectMySQL.java +++ b/src/com/iciql/SQLDialectMySQL.java @@ -17,7 +17,6 @@ package com.iciql; import com.iciql.TableDefinition.FieldDefinition; -import com.iciql.TableDefinition.IndexDefinition; import com.iciql.util.StatementBuilder; /** @@ -33,52 +32,22 @@ public class SQLDialectMySQL extends SQLDialectDefault { return sqlType; } - @Override - public boolean supportsMemoryTables() { - return false; - } - @Override public String prepareColumnName(String name) { return "`" + name + "`"; } @Override - protected boolean prepareColumnDefinition(StatementBuilder buff, boolean isAutoIncrement, + protected boolean prepareColumnDefinition(StatementBuilder buff, String dataType, boolean isAutoIncrement, boolean isPrimaryKey) { - if (isAutoIncrement) { + String convertedType = convertSqlType(dataType); + buff.append(convertedType); + if (isIntegerType(dataType) && 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) { - 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(prepareColumnName(col)); - } - buff.append(") "); - stat.setSQL(buff.toString().trim()); - } - @Override public void prepareMerge(SQLStatement stat, String schemaName, String tableName, TableDefinition def, Object obj) { diff --git a/src/com/iciql/SQLDialectPostgreSQL.java b/src/com/iciql/SQLDialectPostgreSQL.java new file mode 100644 index 0000000..0874a49 --- /dev/null +++ b/src/com/iciql/SQLDialectPostgreSQL.java @@ -0,0 +1,110 @@ +/* + * 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; + +/** + * PostgreSQL database dialect. + */ +public class SQLDialectPostgreSQL extends SQLDialectDefault { + + @Override + public Class getDateTimeClass() { + return java.sql.Timestamp.class; + } + + @Override + public boolean supportsIfNotExists() { + return false; + } + + @Override + public String convertSqlType(String sqlType) { + if ("DOUBLE".equals(sqlType)) { + return "DOUBLE PRECISION"; + } else if ("TINYINT".equals(sqlType)) { + // PostgreSQL does not have a byte type + return "SMALLINT"; + } else if ("CLOB".equals(sqlType)) { + return "TEXT"; + } else if ("BLOB".equals(sqlType)) { + return "BYTEA"; + } + return sqlType; + } + + @Override + protected boolean prepareColumnDefinition(StatementBuilder buff, String dataType, + boolean isAutoIncrement, boolean isPrimaryKey) { + boolean isIdentity = false; + String convertedType = convertSqlType(dataType); + if (isIntegerType(dataType)) { + if (isAutoIncrement && isPrimaryKey) { + if ("BIGINT".equals(dataType)) { + buff.append("BIGSERIAL"); + } else { + buff.append("SERIAL"); + } + isIdentity = true; + } else { + buff.append(convertedType); + } + } else { + buff.append(convertedType); + } + return isIdentity; + } + + @Override + public void prepareCreateIndex(SQLStatement stat, String schemaName, String tableName, + IndexDefinition index) { + StatementBuilder buff = new StatementBuilder(); + buff.append("CREATE "); + switch (index.type) { + 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(tableName); + + switch (index.type) { + case HASH: + buff.append(" USING HASH"); + break; + case UNIQUE_HASH: + buff.append(" USING HASH"); + break; + } + + buff.append(" ("); + for (String col : index.columnNames) { + buff.appendExceptFirst(", "); + buff.append(prepareColumnName(col)); + } + buff.append(") "); + + stat.setSQL(buff.toString().trim()); + } +} \ No newline at end of file diff --git a/src/com/iciql/TableDefinition.java b/src/com/iciql/TableDefinition.java index b6084ca..6f201ad 100644 --- a/src/com/iciql/TableDefinition.java +++ b/src/com/iciql/TableDefinition.java @@ -552,7 +552,7 @@ public class TableDefinition { try { stat.executeUpdate(); } catch (IciqlException e) { - if (e.getIciqlCode() != IciqlException.CODE_TABLE_ALREADY_EXISTS) { + if (e.getIciqlCode() != IciqlException.CODE_OBJECT_ALREADY_EXISTS) { throw e; } } @@ -565,7 +565,7 @@ public class TableDefinition { try { stat.executeUpdate(); } catch (IciqlException e) { - if (e.getIciqlCode() != IciqlException.CODE_INDEX_ALREADY_EXISTS + if (e.getIciqlCode() != IciqlException.CODE_OBJECT_ALREADY_EXISTS && e.getIciqlCode() != IciqlException.CODE_DUPLICATE_KEY) { throw e; } diff --git a/src/com/iciql/TableInspector.java b/src/com/iciql/TableInspector.java index 093055c..ce94c1b 100644 --- a/src/com/iciql/TableInspector.java +++ b/src/com/iciql/TableInspector.java @@ -56,18 +56,15 @@ public class TableInspector { private String schema; private String table; - private boolean forceUpperCase; private Class dateTimeClass; private List primaryKeys = Utils.newArrayList(); private Map indexes; private Map columns; private final String eol = "\n"; - TableInspector(String schema, String table, boolean forceUpperCase, - Class dateTimeClass) { + TableInspector(String schema, String table, Class dateTimeClass) { this.schema = schema; this.table = table; - this.forceUpperCase = forceUpperCase; this.dateTimeClass = dateTimeClass; } @@ -121,7 +118,7 @@ public class TableInspector { if (info.type.equals(IndexType.UNIQUE)) { String name = info.name.toLowerCase(); if (name.startsWith("primary") || name.startsWith("sys_idx_sys_pk") - || name.startsWith("sql")) { + || name.startsWith("sql") || name.endsWith("_pkey")) { // skip primary key indexes continue; } @@ -166,7 +163,7 @@ public class TableInspector { if (!col.isAutoIncrement) { col.defaultValue = rs.getString("COLUMN_DEF"); } - columns.put(col.name, col); + columns.put(col.name.toLowerCase(), col); } } finally { closeSilently(rs); @@ -478,13 +475,12 @@ public class TableInspector { */ private void validate(List remarks, FieldDefinition fieldDef, boolean throwError) { // unknown field - String field = forceUpperCase ? fieldDef.columnName.toUpperCase() : fieldDef.columnName; - if (!columns.containsKey(field)) { + if (!columns.containsKey(fieldDef.columnName.toLowerCase())) { // unknown column mapping remarks.add(error(table, fieldDef, "Does not exist in database!").throwError(throwError)); return; } - ColumnInspector col = columns.get(field); + ColumnInspector col = columns.get(fieldDef.columnName.toLowerCase()); Class fieldClass = fieldDef.field.getType(); Class jdbcClass = ModelUtils.getClassForSqlType(col.type, dateTimeClass); diff --git a/src/com/iciql/build/Build.java b/src/com/iciql/build/Build.java index 8dfd7df..75f6483 100644 --- a/src/com/iciql/build/Build.java +++ b/src/com/iciql/build/Build.java @@ -59,6 +59,7 @@ public class Build { downloadFromApache(MavenObject.HSQLDB, BuildType.RUNTIME); downloadFromApache(MavenObject.DERBY, BuildType.RUNTIME); downloadFromApache(MavenObject.MYSQL, BuildType.RUNTIME); + downloadFromApache(MavenObject.POSTGRESQL, BuildType.RUNTIME); downloadFromApache(MavenObject.JCOMMANDER, BuildType.RUNTIME); downloadFromApache(MavenObject.JCOMMANDER, BuildType.COMPILETIME); downloadFromApache(MavenObject.MARKDOWNPAPERS, BuildType.RUNTIME); @@ -184,6 +185,9 @@ public class Build { public static final MavenObject MYSQL = new MavenObject("mysql", "mysql-connector-java", "5.1.15", "0fbc80454d27cc65f3addfa516707e9f8e60c3eb", "", ""); + public static final MavenObject POSTGRESQL = new MavenObject("postgresql", "postgresql", "9.0-801.jdbc4", + "153f2f92a786f12fc111d0111f709012df87c808", "", ""); + 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 566c6f4..44792c7 100644 --- a/src/com/iciql/util/Utils.java +++ b/src/com/iciql/util/Utils.java @@ -248,7 +248,7 @@ public class Utils { f = Float.parseFloat(s); } catch (Exception e) { } - return f > 0 || s.equals("true") || s.equals("yes"); + return f > 0 || s.equals("true") || s.equals("yes") || s.equals("y") || s.equals("on"); } } diff --git a/tests/com/iciql/test/AnnotationsTest.java b/tests/com/iciql/test/AnnotationsTest.java index 4f50d61..2fdbcf4 100644 --- a/tests/com/iciql/test/AnnotationsTest.java +++ b/tests/com/iciql/test/AnnotationsTest.java @@ -17,9 +17,7 @@ package com.iciql.test; -import static com.iciql.test.IciqlSuite.assertStartsWith; import static org.junit.Assert.assertEquals; -import static org.junit.Assert.assertFalse; import static org.junit.Assert.assertTrue; import java.sql.DatabaseMetaData; @@ -38,6 +36,7 @@ import com.iciql.test.models.ProductAnnotationOnly; import com.iciql.test.models.ProductInheritedAnnotation; import com.iciql.test.models.ProductMixedAnnotation; import com.iciql.test.models.ProductNoCreateTable; +import com.iciql.util.Utils; /** * Test annotation processing. @@ -68,26 +67,30 @@ public class AnnotationsTest { public void testIndexCreation() throws SQLException { // test indexes are created, and columns are in the right order DatabaseMetaData meta = db.getConnection().getMetaData(); - boolean isH2 = meta.getDatabaseProductName().equals("H2"); String schema = IciqlSuite.getDefaultSchema(db); - ResultSet rs = meta.getIndexInfo(null, schema, "ANNOTATEDPRODUCT", false, true); + boolean toUpper = meta.storesUpperCaseIdentifiers(); + boolean toLower = meta.storesLowerCaseIdentifiers(); + ResultSet rs = meta.getIndexInfo(null, prepName(schema, toUpper, toLower), + prepName("ANNOTATEDPRODUCT", toUpper, toLower), false, true); + + List list = Utils.newArrayList(); + while (rs.next()) { + String col = rs.getString("COLUMN_NAME"); + String index = rs.getString("INDEX_NAME"); + list.add((col + ":" + index).toLowerCase()); + } + assertTrue(list.contains("name:annotatedproduct_0")); + assertTrue(list.contains("cat:annotatedproduct_0")); + assertTrue(list.contains("name:nameidx")); + } - // first index is primary key index - // H2 gives this a testable name. - assertTrue(rs.next()); - if (isH2) { - assertStartsWith(rs.getString("INDEX_NAME").toUpperCase(), "PRIMARY_KEY"); + private String prepName(String name, boolean upper, boolean lower) { + if (upper) { + return name.toUpperCase(); + } else if (lower) { + return name.toLowerCase(); } - assertTrue(rs.next()); - assertStartsWith(rs.getString("INDEX_NAME").toUpperCase(), "ANNOTATEDPRODUCT_0"); - assertStartsWith(rs.getString("COLUMN_NAME").toUpperCase(), "NAME"); - assertTrue(rs.next()); - assertStartsWith(rs.getString("INDEX_NAME").toUpperCase(), "ANNOTATEDPRODUCT_0"); - assertStartsWith(rs.getString("COLUMN_NAME").toUpperCase(), "CAT"); - assertTrue(rs.next()); - assertStartsWith(rs.getString("INDEX_NAME").toUpperCase(), "NAMEIDX"); - assertStartsWith(rs.getString("COLUMN_NAME").toUpperCase(), "NAME"); - assertFalse(rs.next()); + return name; } @Test @@ -180,7 +183,7 @@ public class AnnotationsTest { try { db.insertAll(ProductNoCreateTable.getList()); } catch (IciqlException e) { - assertEquals(IciqlException.CODE_TABLE_NOT_FOUND, e.getIciqlCode()); + assertEquals(IciqlException.CODE_OBJECT_NOT_FOUND, e.getIciqlCode()); } } diff --git a/tests/com/iciql/test/IciqlSuite.java b/tests/com/iciql/test/IciqlSuite.java index 0389d80..d79f277 100644 --- a/tests/com/iciql/test/IciqlSuite.java +++ b/tests/com/iciql/test/IciqlSuite.java @@ -78,9 +78,10 @@ public class IciqlSuite { private static final TestDb[] TEST_DBS = { new TestDb("H2 (embedded)", "jdbc:h2:mem:db{0,number,000}"), new TestDb("HSQL (embedded)", "jdbc:hsqldb:mem:db{0,number,000}"), new TestDb("Derby (embedded)", "jdbc:derby:memory:db{0,number,000};create=true"), - new TestDb("MySQL (tcp/myisam)", "jdbc:mysql://localhost:3306/iciql") }; + new TestDb("MySQL (tcp/myisam)", "jdbc:mysql://localhost:3306/iciql"), + new TestDb("PostgreSQL (tcp)", "jdbc:postgresql://localhost:5432/iciql")}; - private static final TestDb DEFAULT_TEST_DB = TEST_DBS[0]; + private static final TestDb DEFAULT_TEST_DB = TEST_DBS[4]; private static final PrintStream ERR = System.err; @@ -96,6 +97,11 @@ public class IciqlSuite { Assert.assertTrue(MessageFormat.format("Expected \"{0}\", got: \"{1}\"", startsWith, value), value.startsWith(startsWith)); } + + public static void assertEqualsIgnoreCase(String expected, String actual) { + Assert.assertTrue(MessageFormat.format("Expected \"{0}\", got: \"{1}\"", expected, actual), + expected.equalsIgnoreCase(actual)); + } public static boolean equivalentTo(double expected, double actual) { if (Double.compare(expected, actual) == 0) { diff --git a/tests/com/iciql/test/ModelsTest.java b/tests/com/iciql/test/ModelsTest.java index d2e02fa..c5569d6 100644 --- a/tests/com/iciql/test/ModelsTest.java +++ b/tests/com/iciql/test/ModelsTest.java @@ -19,6 +19,7 @@ package com.iciql.test; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertTrue; +import static com.iciql.test.IciqlSuite.assertEqualsIgnoreCase; import java.sql.SQLException; import java.text.MessageFormat; @@ -93,7 +94,8 @@ public class ModelsTest { assertEquals(sb.toString(), expected - 1, remarks.size()); } else { assertEquals(sb.toString(), expected, remarks.size()); - assertEquals(MessageFormat.format("@IQSchema(\"{0}\")", schemaName), remarks.get(0).message); + assertEqualsIgnoreCase(MessageFormat.format("@IQSchema(\"{0}\")", schemaName), + remarks.get(0).message); } } @@ -121,7 +123,7 @@ public class ModelsTest { // a poor test, but a start String dbName = IciqlSuite.getDatabaseEngineName(db); if (dbName.equals("H2")) { - assertEquals(1478, models.get(0).length()); + assertEquals(1475, models.get(0).length()); } else if (dbName.startsWith("HSQL")) { // HSQL uses Double instead of Float assertEquals(1479, models.get(0).length()); @@ -129,6 +131,15 @@ public class ModelsTest { // Derby uses java.sql.Timestamp not java.util.Date // Derby uses username as schema name assertEquals(1489, models.get(0).length()); + } else if (dbName.equals("PostgreSQL")) { + assertEquals(1514, models.get(0).length()); + } else if (dbName.equals("MySQL")) { + // MySQL uses timestamp default values like + // 0000-00-00 00:00:00 and CURRENT_TIMESTAMP + assertEquals(1561, models.get(0).length()); + } else { + // unknown database + assertEquals(0, models.get(0).length()); } } } diff --git a/tests/com/iciql/test/models/ProductAnnotationOnly.java b/tests/com/iciql/test/models/ProductAnnotationOnly.java index e4de22d..1f6b4e2 100644 --- a/tests/com/iciql/test/models/ProductAnnotationOnly.java +++ b/tests/com/iciql/test/models/ProductAnnotationOnly.java @@ -37,7 +37,7 @@ public class ProductAnnotationOnly { public String unmappedField; @IQColumn(name = "id", autoIncrement = true) - public Integer productId; + public Long productId; @IQColumn(name = "cat", length = 15, trim = true) public String category; @@ -56,7 +56,7 @@ public class ProductAnnotationOnly { // public constructor } - private ProductAnnotationOnly(int productId, String productName, String category, double unitPrice, + private ProductAnnotationOnly(long productId, String productName, String category, double unitPrice, int unitsInStock, String unmappedField) { this.productId = productId; this.productName = productName; -- 2.39.5