From d18e27e799f4d887b22a5fc3cfcf59ddadde92f1 Mon Sep 17 00:00:00 2001 From: James Moger Date: Tue, 31 Jan 2017 19:38:17 -0500 Subject: [PATCH] Adjust CREATE TABLE column constraint syntax for MySQL 5.7 strict mode --- src/main/java/com/iciql/SQLDialect.java | 15 +++++++ .../java/com/iciql/SQLDialectDefault.java | 39 ++++++++++++------- src/main/java/com/iciql/SQLDialectDerby.java | 22 +++++++++++ src/test/java/com/iciql/test/ModelsTest.java | 2 +- 4 files changed, 62 insertions(+), 16 deletions(-) diff --git a/src/main/java/com/iciql/SQLDialect.java b/src/main/java/com/iciql/SQLDialect.java index 081b7e6..76450cd 100644 --- a/src/main/java/com/iciql/SQLDialect.java +++ b/src/main/java/com/iciql/SQLDialect.java @@ -20,6 +20,7 @@ package com.iciql; import com.iciql.Iciql.DataTypeAdapter; import com.iciql.TableDefinition.IndexDefinition; +import com.iciql.util.StatementBuilder; import java.sql.ResultSet; @@ -206,4 +207,18 @@ public interface SQLDialect { void prepareBitwiseXor(SQLStatement stat, Query query, A x, A y); + /** + * Specifies the syntax for a column constraint. + * + * @param isAutoIncrement + * @param isPrimaryKey + * @param isNullable + * @param fieldType + * @param dataType + * @param defaultValue + * @return the column constraint + */ + String prepareColumnConstraint(boolean isAutoIncrement, boolean isPrimaryKey, boolean isNullable, + Class fieldType, String dataType, String defaultValue); + } diff --git a/src/main/java/com/iciql/SQLDialectDefault.java b/src/main/java/com/iciql/SQLDialectDefault.java index 98f63c4..cd9eeaf 100644 --- a/src/main/java/com/iciql/SQLDialectDefault.java +++ b/src/main/java/com/iciql/SQLDialectDefault.java @@ -172,21 +172,9 @@ public class SQLDialectDefault implements SQLDialect { hasIdentityColumn |= prepareColumnDefinition(buff, convertSqlType(dataType), field.isAutoIncrement, field.isPrimaryKey); } - - // 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 (!field.nullable) { - buff.append(" NOT NULL"); - } + + buff.append(prepareColumnConstraint(field.isAutoIncrement, field.isPrimaryKey, field.nullable, field.field.getType(), + field.dataType, field.defaultValue)); } // if table does not have identity column then specify primary key @@ -368,6 +356,27 @@ public class SQLDialectDefault implements SQLDialect { return false; } + @Override + public String prepareColumnConstraint(boolean isAutoIncrement, boolean isPrimaryKey, boolean nullable, Class fieldType, String dataType, String defaultValue) { + StringBuilder sb = new StringBuilder(); + if (!isAutoIncrement && !isPrimaryKey) { + + if (nullable && (defaultValue == null || ((dataType.equals("TIMESTAMP") || dataType.equals("DATETIME")) && StringUtils.isNullOrEmpty(defaultValue)))) { + sb.append(" NULL"); + } else if (!StringUtils.isNullOrEmpty(defaultValue)) { + if (ModelUtils.isProperlyFormattedDefaultValue(defaultValue) + && ModelUtils.isValidDefaultValue(fieldType, defaultValue)) { + sb.append(" DEFAULT ").append(defaultValue); + } + } + } + + if (!nullable) { + sb.append(" NOT NULL"); + } + return sb.toString(); + } + @Override public void prepareCreateIndex(SQLStatement stat, String schemaName, String tableName, IndexDefinition index) { diff --git a/src/main/java/com/iciql/SQLDialectDerby.java b/src/main/java/com/iciql/SQLDialectDerby.java index fcaee1e..8ee395a 100644 --- a/src/main/java/com/iciql/SQLDialectDerby.java +++ b/src/main/java/com/iciql/SQLDialectDerby.java @@ -17,6 +17,7 @@ package com.iciql; import com.iciql.util.StatementBuilder; +import com.iciql.util.StringUtils; /** * Derby database dialect. @@ -61,6 +62,27 @@ public class SQLDialectDerby extends SQLDialectDefault { return false; } + @Override + public String prepareColumnConstraint(boolean isAutoIncrement, boolean isPrimaryKey, boolean nullable, Class fieldType, String dataType, String defaultValue) { + StringBuilder sb = new StringBuilder(); + if (!isAutoIncrement && !isPrimaryKey) { + + if (nullable && (defaultValue == null || ((dataType.equals("TIMESTAMP") || dataType.equals("DATETIME")) && StringUtils.isNullOrEmpty(defaultValue)))) { + sb.append(" DEFAULT NULL"); + } else if (!StringUtils.isNullOrEmpty(defaultValue)) { + if (ModelUtils.isProperlyFormattedDefaultValue(defaultValue) + && ModelUtils.isValidDefaultValue(fieldType, defaultValue)) { + sb.append(" DEFAULT ").append(defaultValue); + } + } + } + + if (!nullable) { + sb.append(" NOT NULL"); + } + return sb.toString(); + } + @Override public void prepareDropTable(SQLStatement stat, TableDefinition def) { StatementBuilder buff = new StatementBuilder("DROP TABLE " diff --git a/src/test/java/com/iciql/test/ModelsTest.java b/src/test/java/com/iciql/test/ModelsTest.java index 9fef979..5f77e97 100644 --- a/src/test/java/com/iciql/test/ModelsTest.java +++ b/src/test/java/com/iciql/test/ModelsTest.java @@ -133,7 +133,7 @@ public class ModelsTest { } else if (dbName.equals("Apache Derby")) { // Derby uses java.sql.Timestamp not java.util.Date // Derby uses username as schema name - assertEquals(1601, models.get(0).length()); + assertEquals(1643, models.get(0).length()); } else if (dbName.equals("PostgreSQL")) { assertEquals(1643, models.get(0).length()); } else if (dbName.equals("MySQL")) { -- 2.39.5