Adjust CREATE TABLE column constraint syntax for MySQL 5.7 strict mode

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 {
 
     <T, A> void prepareBitwiseXor(SQLStatement stat, Query<T> 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
@@ -369,6 +357,27 @@ public class SQLDialectDefault implements SQLDialect {
     }
 
     @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) {
         StatementBuilder buff = new StatementBuilder();
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.

@@ -62,6 +63,27 @@ public class SQLDialectDerby extends SQLDialectDefault {
     }

 

     @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 <T> void prepareDropTable(SQLStatement stat, TableDefinition<T> def) {

         StatementBuilder buff = new StatementBuilder("DROP TABLE "

                 + prepareTableName(def.schemaName, def.tableName));

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")) {