svn commit: r1793300 - in /ofbiz/ofbiz-framework/trunk/framework: entity/fieldtype/fieldtypemysql.xml entityext/servicedef/services.xml entityext/src/main/java/org/apache/ofbiz/entityext/data/UpgradeServices.java

[Deepak Dixit-5]

Author: deepak
Date: Mon May  1 10:16:52 2017
New Revision: 1793300

URL: http://svn.apache.org/viewvc?rev=1793300&view=rev
Log:
Improved: Updated msyql sql-type for datetime field-type to support Fractional Seconds in Time Values (OFBIZ-9337)

Added:
    ofbiz/ofbiz-framework/trunk/framework/entityext/src/main/java/org/apache/ofbiz/entityext/data/UpgradeServices.java   (with props)
Modified:
    ofbiz/ofbiz-framework/trunk/framework/entity/fieldtype/fieldtypemysql.xml
    ofbiz/ofbiz-framework/trunk/framework/entityext/servicedef/services.xml

Modified: ofbiz/ofbiz-framework/trunk/framework/entity/fieldtype/fieldtypemysql.xml
URL: http://svn.apache.org/viewvc/ofbiz/ofbiz-framework/trunk/framework/entity/fieldtype/fieldtypemysql.xml?rev=1793300&r1=1793299&r2=1793300&view=diff
==============================================================================
--- ofbiz/ofbiz-framework/trunk/framework/entity/fieldtype/fieldtypemysql.xml (original)
+++ ofbiz/ofbiz-framework/trunk/framework/entity/fieldtype/fieldtypemysql.xml Mon May  1 10:16:52 2017
@@ -26,9 +26,9 @@ under the License.
     <field-type-def type="byte-array" sql-type="LONGBLOB" java-type="byte[]"/>
     <field-type-def type="object" sql-type="LONGBLOB" java-type="Object"/>
 
-    <field-type-def type="date-time" sql-type="DATETIME" java-type="java.sql.Timestamp"/>
+    <field-type-def type="date-time" sql-type="DATETIME(3)" java-type="java.sql.Timestamp"/>
     <field-type-def type="date" sql-type="DATE" java-type="java.sql.Date"/>
-    <field-type-def type="time" sql-type="TIME" java-type="java.sql.Time"/>
+    <field-type-def type="time" sql-type="TIME(3)" java-type="java.sql.Time"/>
 
     <field-type-def type="currency-amount" sql-type="DECIMAL(18,2)" java-type="java.math.BigDecimal"/>
     <field-type-def type="currency-precise" sql-type="DECIMAL(18,3)" java-type="java.math.BigDecimal"/>

Modified: ofbiz/ofbiz-framework/trunk/framework/entityext/servicedef/services.xml
URL: http://svn.apache.org/viewvc/ofbiz/ofbiz-framework/trunk/framework/entityext/servicedef/services.xml?rev=1793300&r1=1793299&r2=1793300&view=diff
==============================================================================
--- ofbiz/ofbiz-framework/trunk/framework/entityext/servicedef/services.xml (original)
+++ ofbiz/ofbiz-framework/trunk/framework/entityext/servicedef/services.xml Mon May  1 10:16:52 2017
@@ -461,4 +461,11 @@ under the License.
         <description>Entity sync permission Checking Logic</description>
         <implements service="permissionInterface"/>
     </service>
+    <service name="generateMySqlFileWithAlterTableForTimestamps" engine="java" auth="true" transaction-timeout="14400"
+            location="org.apache.ofbiz.entityext.data.UpgradeServices" invoke="generateMySqlFileWithAlterTableForTimestamps">
+        <description>mysql timestamp Field migration service,
+            it will generate sql file with alter query statement to update the datatype of timestamp field to support Fractional Seconds in Time Values
+            mySql 5.6.4 added support for Fractional Seconds in Time Values. </description>
+        <attribute name="groupName" type="String" mode="IN" optional="true" default-value="org.apache.ofbiz"/>
+    </service>
 </services>

Added: ofbiz/ofbiz-framework/trunk/framework/entityext/src/main/java/org/apache/ofbiz/entityext/data/UpgradeServices.java
URL: http://svn.apache.org/viewvc/ofbiz/ofbiz-framework/trunk/framework/entityext/src/main/java/org/apache/ofbiz/entityext/data/UpgradeServices.java?rev=1793300&view=auto
==============================================================================
--- ofbiz/ofbiz-framework/trunk/framework/entityext/src/main/java/org/apache/ofbiz/entityext/data/UpgradeServices.java (added)
+++ ofbiz/ofbiz-framework/trunk/framework/entityext/src/main/java/org/apache/ofbiz/entityext/data/UpgradeServices.java Mon May  1 10:16:52 2017
@@ -0,0 +1,110 @@
+package org.apache.ofbiz.entityext.data;
+
+import java.util.List;
+import java.util.Locale;
+import java.util.Map;
+
+import org.apache.ofbiz.base.util.Debug;
+import org.apache.ofbiz.base.util.UtilMisc;
+import org.apache.ofbiz.base.util.UtilProperties;
+import org.apache.ofbiz.entity.Delegator;
+import org.apache.ofbiz.entity.GenericEntityException;
+import org.apache.ofbiz.entity.GenericValue;
+import org.apache.ofbiz.entity.model.ModelEntity;
+import org.apache.ofbiz.entity.model.ModelField;
+import org.apache.ofbiz.security.Security;
+import org.apache.ofbiz.service.DispatchContext;
+import org.apache.ofbiz.service.ServiceUtil;
+
+import java.io.BufferedWriter;
+import java.io.File;
+import java.io.FileNotFoundException;
+import java.io.FileOutputStream;
+import java.io.OutputStreamWriter;
+import java.io.PrintWriter;
+import java.io.UnsupportedEncodingException;
+
+/**
+ * Entity Data Upgrade Services
+ *
+ */
+
+public class UpgradeServices {
+    public static final String module = UpgradeServices.class.getName();
+    public static final String resource = "EntityExtUiLabels";
+
+
+    /**
+     * Generate sql file for data migration from mySql.5 and earlier version to mySql.6 to later version
+     * mySql added support in 5.6 to support microseconds for datetime field.
+     * https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html
+     * <ul>
+     * <li>Service will take groupName as in param,</li>
+     * <li>iterate all the entity and check for datetime and time field</li>
+     * <li>it will generate alter table sql statement to update the field data type</li>
+     * <li>datetime will be altered with DATETIME(3)</li>
+     * <li>time will be altered with TIME(3)</li>
+     * <li>sql fiel will be created at following location</li>
+     * <li>${ofbiz.home}/runtime/tempfiles/<groupName>.sql</></li>
+     * </ul>
+     * @param dctx
+     * @param context
+     * @return Map with the success result of the service,
+     */
+    public static Map<String, Object> generateMySqlFileWithAlterTableForTimestamps (DispatchContext dctx, Map<String, Object> context) {
+        Delegator delegator = dctx.getDelegator();
+        Security security = dctx.getSecurity();
+        Locale locale = (Locale) context.get("locale");
+
+        // check permission
+        GenericValue userLogin = (GenericValue) context.get("userLogin");
+        if (!security.hasPermission("ENTITY_MAINT", userLogin)) {
+            Debug.logError(UtilProperties.getMessage(resource, "EntityExtServicePermissionNotGranted", locale), module);
+            return ServiceUtil.returnError(UtilProperties.getMessage(resource, "EntityExtServicePermissionNotGranted", locale));
+        }
+
+        String groupName = (String) context.get("groupName");
+
+        Map<String, ModelEntity> modelEntities;
+        PrintWriter dataWriter = null;
+        try {
+            modelEntities = delegator.getModelEntityMapByGroup(groupName);
+            dataWriter = new PrintWriter(new BufferedWriter(new OutputStreamWriter(
+                    new FileOutputStream(new File(System.getProperty("ofbiz.home") + "/runtime/tempfiles/" + groupName + ".sql")), "UTF-8")));
+
+            /* TODO:
+            1) fetch the meta data of the "date-time" field using the JDBC connection and JDBC meta data;
+            2) compare it to date-time and only generate the alter statement if they differs;
+            */
+
+            dataWriter.println("SET FOREIGN_KEY_CHECKS=0;");
+            for (ModelEntity modelEntity: modelEntities.values()) {
+                List<ModelField> fields = modelEntity.getFieldsUnmodifiable();
+                for (ModelField field: fields) {
+                    if (modelEntity.getPlainTableName() != null) {
+                        if ("date-time".equals(field.getType())) {
+                            dataWriter.println("ALTER TABLE " + modelEntity.getPlainTableName() + " MODIFY " + field.getColName() + " DATETIME(3);");
+                        }
+                        if ("time".equals(field.getType())) {
+                            dataWriter.println("ALTER TABLE " + modelEntity.getPlainTableName() + " MODIFY " + field.getColName() + " TIME(3);");
+                        }
+                    }
+                }
+            }
+            dataWriter.println("SET FOREIGN_KEY_CHECKS=1;");
+            dataWriter.close();
+        } catch (GenericEntityException e) {
+            Debug.logError(e, "Error getting list of entities in group: " + e.toString(), module);
+            return ServiceUtil.returnError(UtilProperties.getMessage(resource, "EntityExtErrorGettingListOfEntityInGroup", UtilMisc.toMap("errorString", e.toString()), locale));
+        } catch (FileNotFoundException | UnsupportedEncodingException e) {
+            Debug.logError(e, e.getMessage(), module);
+            return ServiceUtil.returnError(e.getMessage());
+        } finally {
+            if (dataWriter != null)
+                dataWriter.close();
+        }
+
+        return ServiceUtil.returnSuccess();
+    }
+
+}

Propchange: ofbiz/ofbiz-framework/trunk/framework/entityext/src/main/java/org/apache/ofbiz/entityext/data/UpgradeServices.java
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: ofbiz/ofbiz-framework/trunk/framework/entityext/src/main/java/org/apache/ofbiz/entityext/data/UpgradeServices.java
------------------------------------------------------------------------------
    svn:keywords = Date Rev Author URL Id

Propchange: ofbiz/ofbiz-framework/trunk/framework/entityext/src/main/java/org/apache/ofbiz/entityext/data/UpgradeServices.java
------------------------------------------------------------------------------
    svn:mime-type = text/plain