View Issue Details

IDProjectCategoryView StatusLast Update
0002367SymmetricDSNew Featurepublic2015-12-10 03:00
Reportermsc Assigned Tochenson  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.7.20 
Target Version3.7.22Fixed in Version3.7.22 
Summary0002367: TableTransformation operation change support for UPDATE-DML (in addition to DELETE)
Descriptioncurrently an operation change is only possible for the delete statement.
But for one of my use-cases i need to change an UPDATE statements with a transformation into INSERT or DELETE statements.

for example you can set the update_action column of sym_transform_table to:

if ("1".equals(ATTENDED)) { return "INS_ROW"; } else { return "DEL_ROW"; }

and for a source-table where the (boolean) attribute attended can be switched on and off you can insert and delete an entry on the target table.

i've implemented a test-case too for this new transform writer option.
TagsNo tags attached.

Activities

msc

2015-08-10 11:10

reporter  

0001-Support-for-TableTransform-UpdateAction-with-Bean-Sh.patch (30,326 bytes)   
From c550bd21e5de4f84723383dd02c2f1037f132f84 Mon Sep 17 00:00:00 2001
From: Markus Schulz <msc@onesty-tech.de>
Date: Mon, 10 Aug 2015 12:50:29 +0200
Subject: [PATCH] Support for TableTransform UpdateAction with
 Bean-Shell-Scripts.

---
 .../src/asciidoc/configuration/transforms.ad       |    2 +-
 .../configuration/transforms/operation-change.ad   |    4 +-
 .../configuration/transforms/virtual-columns.ad    |    4 +-
 symmetric-assemble/src/docbook/configuration.xml   |   21 ++++++
 .../symmetric/service/impl/TransformService.java   |    9 ++-
 .../service/impl/TransformServiceSqlMap.java       |    9 ++-
 .../src/main/resources/symmetric-schema.xml        |    1 +
 .../symmetric/io/data/transform/DeleteAction.java  |   27 -------
 .../io/data/transform/TargetDmlAction.java         |   27 +++++++
 .../io/data/transform/TransformTable.java          |   77 ++++++++++++++++++--
 .../symmetric/io/data/writer/TransformWriter.java  |   32 +++++---
 .../io/data/writer/TransformWriterTest.java        |   65 +++++++++--------
 12 files changed, 193 insertions(+), 85 deletions(-)
 delete mode 100644 symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/DeleteAction.java
 create mode 100644 symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TargetDmlAction.java

diff --git a/symmetric-assemble/src/asciidoc/configuration/transforms.ad b/symmetric-assemble/src/asciidoc/configuration/transforms.ad
index b392e9f..bb1b0f9 100644
--- a/symmetric-assemble/src/asciidoc/configuration/transforms.ad
+++ b/symmetric-assemble/src/asciidoc/configuration/transforms.ad
@@ -112,7 +112,7 @@ ifndef::pro[]
 ----
 insert into SYM_TRANSFORM_TABLE (
 	transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name,
-	target_table_name, delete_action, transform_order, column_policy, update_first,
+	target_table_name, update_action, delete_action, transform_order, column_policy, update_first,
 	last_update_by, last_update_time, create_time
 ) values (
 	'itemSellingPriceTransform', 'store', 'corp', 'EXTRACT', 'ITEM_SELLING_PRICE',
diff --git a/symmetric-assemble/src/asciidoc/configuration/transforms/operation-change.ad b/symmetric-assemble/src/asciidoc/configuration/transforms/operation-change.ad
index b422c15..29a9387 100644
--- a/symmetric-assemble/src/asciidoc/configuration/transforms/operation-change.ad
+++ b/symmetric-assemble/src/asciidoc/configuration/transforms/operation-change.ad
@@ -26,7 +26,7 @@ ifndef::pro[]
 ----
 insert into SYM_TRANSFORM_TABLE (
 	transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name,
-	target_table_name, delete_action, transform_order, column_policy, update_first,
+	target_table_name, update_action, delete_action, transform_order, column_policy, update_first,
 	last_update_time, create_time
 ) values (
 	'update-first', 'store', 'corp', 'EXTRACT', 'ITEM_SELLING_PRICE',
@@ -48,7 +48,7 @@ ifndef::pro[]
 ----
 insert into SYM_TRANSFORM_TABLE (
 	transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name,
-	target_table_name, delete_action, transform_order, column_policy, update_first,
+	target_table_name, update_action, delete_action, transform_order, column_policy, update_first,
 	last_update_time, create_time
 ) values (
 	'delete-action-update-col', 'store', 'corp', 'EXTRACT', 'ITEM_SELLING_PRICE',
diff --git a/symmetric-assemble/src/asciidoc/configuration/transforms/virtual-columns.ad b/symmetric-assemble/src/asciidoc/configuration/transforms/virtual-columns.ad
index 1e73c5f..ab32993 100644
--- a/symmetric-assemble/src/asciidoc/configuration/transforms/virtual-columns.ad
+++ b/symmetric-assemble/src/asciidoc/configuration/transforms/virtual-columns.ad
@@ -22,7 +22,7 @@ ifndef::pro[]
 ----
 insert into SYM_TRANSFORM_TABLE (
 	transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name,
-	target_table_name, delete_action, transform_order, column_policy, update_first,
+	target_table_name, update_action, delete_action, transform_order, column_policy, update_first,
 	last_update_by, last_update_time, create_time
 ) values (
 	'extractStoreItemSellingPriceTransform', 'store', 'corp', 'EXTRACT', 'ITEM_SELLING_PRICE',
@@ -35,7 +35,7 @@ insert into SYM_TRANSFORM_TABLE (
 ----
 insert into SYM_TRANSFORM_TABLE (
 	transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name,
-	target_table_name, delete_action, transform_order, column_policy, update_first,
+	target_table_name, update_action, delete_action, transform_order, column_policy, update_first,
 	last_update_by, last_update_time, create_time
 ) values (
 	'loadCorpItemSellingPriceTransform', 'corp', 'store', 'LOAD', 'ITEM_SELLING_PRICE',
diff --git a/symmetric-assemble/src/docbook/configuration.xml b/symmetric-assemble/src/docbook/configuration.xml
index 2c3ad84..4bc3d4b 100644
--- a/symmetric-assemble/src/docbook/configuration.xml
+++ b/symmetric-assemble/src/docbook/configuration.xml
@@ -1243,6 +1243,27 @@ column.</listitem>
 </listitem>
 
 <listitem>
+update_action: When a source operation of Update takes place, there are
+three possible ways to handle the transformation at the target. The
+options include:
+<itemizedlist>
+<listitem>NONE - The update results in no target changes.</listitem>
+
+<listitem>DEL_ROW - The update results in a delete of the row
+as specified by the pk columns defined in the transformation
+configuration.</listitem>
+
+<listitem>UPDATE_COL - The update results in an Update
+operation on the target which updates the specific rows and columns
+based on the defined transformation.</listitem>
+
+<listitem>BeanShell Script Transform ('bsh'):
+	script code which returns one of the above items.
+	you can use COLUMN variables inside the script.</listitem>
+</itemizedlist>
+</listitem>
+
+<listitem>
 delete_action: When a source operation of Delete takes place, there are
 three possible ways to handle the transformation at the target. The
 options include:
diff --git a/symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TransformService.java b/symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TransformService.java
index 24d4f93..daa4760 100644
--- a/symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TransformService.java
+++ b/symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TransformService.java
@@ -42,7 +42,7 @@ import org.jumpmind.symmetric.io.data.transform.ColumnsToRowsValueColumnTransfor
 import org.jumpmind.symmetric.io.data.transform.ConstantColumnTransform;
 import org.jumpmind.symmetric.io.data.transform.CopyColumnTransform;
 import org.jumpmind.symmetric.io.data.transform.CopyIfChangedColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.DeleteAction;
+import org.jumpmind.symmetric.io.data.transform.TargetDmlAction;
 import org.jumpmind.symmetric.io.data.transform.IColumnTransform;
 import org.jumpmind.symmetric.io.data.transform.IdentityColumnTransform;
 import org.jumpmind.symmetric.io.data.transform.JavaColumnTransform;
@@ -381,8 +381,8 @@ public class TransformService extends AbstractService implements ITransformServi
         public TransformTableNodeGroupLink mapRow(Row rs) {
             TransformTableNodeGroupLink table = new TransformTableNodeGroupLink();
             table.setTransformId(rs.getString("transform_id"));
-            table.setNodeGroupLink(configurationService.getNodeGroupLinkFor(
-                    rs.getString("source_node_group_id"), rs.getString("target_node_group_id"), false));
+            table.setNodeGroupLink(configurationService
+               .getNodeGroupLinkFor(rs.getString("source_node_group_id"), rs.getString("target_node_group_id"), false));
             table.setSourceCatalogName(rs.getString("source_catalog_name"));
             table.setSourceSchemaName(rs.getString("source_schema_name"));
             table.setSourceTableName(rs.getString("source_table_name"));
@@ -401,7 +401,8 @@ public class TransformService extends AbstractService implements ITransformServi
             table.setTransformOrder(rs.getInt("transform_order"));
             table.setUpdateFirst(rs.getBoolean("update_first"));
             table.setColumnPolicy(ColumnPolicy.valueOf(rs.getString("column_policy")));
-            table.setDeleteAction(DeleteAction.valueOf(rs.getString("delete_action")));
+            table.setUpdateActionBeanScript(rs.getString("update_action"));
+            table.setDeleteAction(TargetDmlAction.valueOf(rs.getString("delete_action")));
             table.setCreateTime(rs.getDateTime("create_time"));
             table.setLastUpdateBy(rs.getString("last_update_by"));
             table.setLastUpdateTime(rs.getDateTime("last_update_time"));
diff --git a/symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TransformServiceSqlMap.java b/symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TransformServiceSqlMap.java
index 47ae43e..91c374e 100644
--- a/symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TransformServiceSqlMap.java
+++ b/symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TransformServiceSqlMap.java
@@ -38,7 +38,7 @@ public class TransformServiceSqlMap extends AbstractSqlMap {
 "  target_table_name,                                          " + 
 "  transform_point,                                            " + 
 "  transform_order,                                            " + 
-"  update_first, delete_action, column_policy,                 " +
+"  update_first, update_action, delete_action, column_policy,  " +
 "  last_update_time, last_update_by, create_time               " + 
 "  from                                                        " + 
 "  $(transform_table) order by transform_order                 " + 
@@ -81,8 +81,9 @@ public class TransformServiceSqlMap extends AbstractSqlMap {
 "  target_table_name=?,       " + 
 "  transform_point=?,         " + 
 "  update_first=?,            " + 
-"  delete_action=?,           " + 
-"  transform_order=?,         " + 
+"  update_action=?,           " +
+"  delete_action=?,           " +
+"  transform_order=?,         " +
 "  column_policy=?,           " +
 "  last_update_time=?,        " +
 "  last_update_by=?           " +
@@ -111,7 +112,7 @@ public class TransformServiceSqlMap extends AbstractSqlMap {
 "  (source_node_group_id, target_node_group_id, source_catalog_name,   " + 
 "  source_schema_name, source_table_name,                              " + 
 "  target_catalog_name, target_schema_name, target_table_name,         " + 
-"  transform_point, update_first, delete_action, transform_order,      " + 
+"  transform_point, update_first, update_action, delete_action, transform_order,      " +
 "  column_policy, last_update_time, last_update_by, create_time, transform_id)                                        " + 
 "  values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)                           " );
 
diff --git a/symmetric-core/src/main/resources/symmetric-schema.xml b/symmetric-core/src/main/resources/symmetric-schema.xml
index 203328b..37fc093 100644
--- a/symmetric-core/src/main/resources/symmetric-schema.xml
+++ b/symmetric-core/src/main/resources/symmetric-schema.xml
@@ -656,6 +656,7 @@
         <column name="target_schema_name" type="VARCHAR" size="255" description="Optional name of the schema a target table is in. Only use this if the target table is not in the default schema." />
         <column name="target_table_name" type="VARCHAR" size="255" description="The name of the target table." />
         <column name="update_first" type="BOOLEANINT" size="1" default="0" description="If true, the target actions are attempted as updates first, regardless of whether the source operation was an insert or an update."/>
+        <column name="update_action" type="VARCHAR" size="255" required="false" default="UPDATE_COL" description="An action to take upon update of a row. Possible values are: DEL_ROW, UPDATE_COL, or NONE." />
         <column name="delete_action" type="VARCHAR" size="10" required="true" description="An action to take upon delete of a row. Possible values are: DEL_ROW, UPDATE_COL, or NONE." />
         <column name="transform_order" type="INTEGER" required="true" default="1" description="Specifies the order in which to apply transforms if more than one target operation occurs."/>
         <column name="column_policy" type="VARCHAR" size="10" default="SPECIFIED" required="true" description="Specifies whether all columns need to be specified or whether they are implied.  Possible values are SPECIFIED or IMPLIED." />
diff --git a/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/DeleteAction.java b/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/DeleteAction.java
deleted file mode 100644
index 3e1813c..0000000
--- a/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/DeleteAction.java
+++ /dev/null
@@ -1,27 +0,0 @@
-/**
- * Licensed to JumpMind Inc under one or more contributor
- * license agreements.  See the NOTICE file distributed
- * with this work for additional information regarding
- * copyright ownership.  JumpMind Inc licenses this file
- * to you under the GNU General Public License, version 3.0 (GPLv3)
- * (the "License"); you may not use this file except in compliance
- * with the License.
- *
- * You should have received a copy of the GNU General Public License,
- * version 3.0 (GPLv3) along with this library; if not, see
- * <http://www.gnu.org/licenses/>.
- *
- * 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 org.jumpmind.symmetric.io.data.transform;
-
-public enum DeleteAction {
-
-    NONE, UPDATE_COL, DEL_ROW;    
-    
-}
diff --git a/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TargetDmlAction.java b/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TargetDmlAction.java
new file mode 100644
index 0000000..9c940cf
--- /dev/null
+++ b/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TargetDmlAction.java
@@ -0,0 +1,27 @@
+/**
+ * Licensed to JumpMind Inc under one or more contributor
+ * license agreements.  See the NOTICE file distributed
+ * with this work for additional information regarding
+ * copyright ownership.  JumpMind Inc licenses this file
+ * to you under the GNU General Public License, version 3.0 (GPLv3)
+ * (the "License"); you may not use this file except in compliance
+ * with the License.
+ *
+ * You should have received a copy of the GNU General Public License,
+ * version 3.0 (GPLv3) along with this library; if not, see
+ * <http://www.gnu.org/licenses/>.
+ *
+ * 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 org.jumpmind.symmetric.io.data.transform;
+
+public enum TargetDmlAction {
+
+    NONE, UPDATE_COL, INS_ROW, UPD_ROW, DEL_ROW;
+    
+}
diff --git a/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TransformTable.java b/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TransformTable.java
index 40eb9f0..d4432a2 100644
--- a/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TransformTable.java
+++ b/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TransformTable.java
@@ -20,16 +20,27 @@
  */
 package org.jumpmind.symmetric.io.data.transform;
 
-import java.util.ArrayList;
-import java.util.Date;
-import java.util.List;
+import java.util.*;
 
+import bsh.Interpreter;
 import org.apache.commons.lang.StringUtils;
 import org.jumpmind.db.model.Table;
+import org.jumpmind.symmetric.io.data.DataContext;
 import org.jumpmind.symmetric.io.data.transform.TransformColumn.IncludeOnType;
+import org.jumpmind.util.Context;
+import org.slf4j.*;
 
 public class TransformTable implements Cloneable {
 
+    final String INTERPRETER_KEY = String.format("%s.BshInterpreter", getClass().getName());
+
+    protected final Logger log = LoggerFactory.getLogger(getClass());
+
+    /*
+     * Static context object used to maintain objects in memory for reference between BSH transforms.
+    */
+    private static Map<String, Object> bshContext = new HashMap<String, Object>();
+
     protected String transformId;
     protected String sourceCatalogName;
     protected String sourceSchemaName;
@@ -40,7 +51,9 @@ public class TransformTable implements Cloneable {
     protected TransformPoint transformPoint;
     protected List<TransformColumn> transformColumns;
     protected List<TransformColumn> primaryKeyColumns;
-    protected DeleteAction deleteAction = DeleteAction.DEL_ROW;
+    protected String updateActionBeanScript = null;
+    protected TargetDmlAction updateAction = TargetDmlAction.UPDATE_COL;
+    protected TargetDmlAction deleteAction = TargetDmlAction.DEL_ROW;
     protected ColumnPolicy columnPolicy = ColumnPolicy.IMPLIED;
     protected boolean updateFirst = false;
     protected int transformOrder = 0;
@@ -198,11 +211,63 @@ public class TransformTable implements Cloneable {
         }
     }
 
-    public void setDeleteAction(DeleteAction deleteAction) {
+    public void setUpdateActionBeanScript(String updateAction) {
+        try {
+            this.updateActionBeanScript = null;
+            this.updateAction = TargetDmlAction.valueOf(updateAction);
+        }
+        catch (IllegalArgumentException e) {
+            //looks like a bean-shell-script
+            this.updateActionBeanScript = updateAction;
+        }
+    }
+
+    public TargetDmlAction evaluateUpdateAction(DataContext dataContext, TransformedData transformedData) {
+        if (updateActionBeanScript != null) {
+            Interpreter interpreter = getInterpreter(dataContext);
+            Map<String, String> sourceValues = transformedData.getSourceValues();
+
+            try {
+                for (String columnName : sourceValues.keySet()) {
+                    interpreter.set(columnName.toUpperCase(), sourceValues.get(columnName));
+                    interpreter.set(columnName, sourceValues.get(columnName));
+                }
+                String transformExpression = updateActionBeanScript;
+                String methodName = String.format("transform_%d()", Math.abs(transformExpression.hashCode()));
+                if (dataContext.get(methodName) == null) {
+                    //create  BSH-Method if not exists in Context
+                    interpreter.set("context", dataContext);
+                    interpreter.set("bshContext", bshContext);
+                    interpreter.eval(String.format("%s {\n%s\n}", methodName, transformExpression));
+                    dataContext.put(methodName, Boolean.TRUE);
+                }
+                //call BSH-Method
+                Object result = interpreter.eval(methodName);
+                //evaluate Result of BSH-Script
+                updateAction = TargetDmlAction.valueOf((String) result);
+            }
+            catch (Exception e) {
+                log.error(e.getMessage(), e);
+            }
+        }
+        return updateAction;
+    }
+
+    protected Interpreter getInterpreter(Context context) {
+        Interpreter interpreter = (Interpreter) context.get(INTERPRETER_KEY);
+        if (interpreter == null) {
+            interpreter = new Interpreter();
+            context.put(INTERPRETER_KEY, interpreter);
+        }
+        return interpreter;
+    }
+
+
+    public void setDeleteAction(TargetDmlAction deleteAction) {
         this.deleteAction = deleteAction;
     }
 
-    public DeleteAction getDeleteAction() {
+    public TargetDmlAction getDeleteAction() {
         return deleteAction;
     }
 
diff --git a/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/TransformWriter.java b/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/TransformWriter.java
index bfd2b37..ddb24eb 100644
--- a/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/TransformWriter.java
+++ b/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/TransformWriter.java
@@ -35,7 +35,7 @@ import org.jumpmind.symmetric.io.data.CsvData;
 import org.jumpmind.symmetric.io.data.DataContext;
 import org.jumpmind.symmetric.io.data.DataEventType;
 import org.jumpmind.symmetric.io.data.IDataWriter;
-import org.jumpmind.symmetric.io.data.transform.DeleteAction;
+import org.jumpmind.symmetric.io.data.transform.TargetDmlAction;
 import org.jumpmind.symmetric.io.data.transform.IColumnTransform;
 import org.jumpmind.symmetric.io.data.transform.IgnoreColumnException;
 import org.jumpmind.symmetric.io.data.transform.IgnoreRowException;
@@ -314,27 +314,41 @@ public class TransformWriter extends NestedDataWriter {
             // perform a transformation if there are columns defined for
             // transformation
             if (data.getColumnNames().length > 0) {
-                if (data.getTargetDmlType() != DataEventType.DELETE) {
-                    persistData = true;
-                } else {
-                    // handle the delete action
-                    DeleteAction deleteAction = transformation.getDeleteAction();
-                    switch (deleteAction) {
+                TargetDmlAction targetAction = null;
+                switch (data.getTargetDmlType()) {
+                    case UPDATE:
+                        targetAction = transformation.evaluateUpdateAction(context, data);
+                        break;
+                    case DELETE:
+                        targetAction = transformation.getDeleteAction();
+                        break;
+                    default:
+                        persistData = true;
+                }
+                if (targetAction != null) {
+                    // how to handle the update/delete action on target..
+                    switch (targetAction) {
                         case DEL_ROW:
                             data.setTargetDmlType(DataEventType.DELETE);
                             persistData = true;
                             break;
                         case UPDATE_COL:
+                        case UPD_ROW:
                             data.setTargetDmlType(DataEventType.UPDATE);
                             persistData = true;
                             break;
+                        case INS_ROW:
+                            data.setTargetDmlType(DataEventType.INSERT);
+                            persistData = true;
+                            break;
                         case NONE:
                         default:
                             if (log.isDebugEnabled()) {
                                 log.debug(
-                                        "The {} transformation is not configured to delete row.  Not sending the delete through.",
-                                        transformation.getTransformId());
+                                   "The {} transformation is not configured to delete row.  Not sending the delete through.",
+                                   transformation.getTransformId());
                             }
+                            break;
                     }
                 }
             }
diff --git a/symmetric-io/src/test/java/org/jumpmind/symmetric/io/data/writer/TransformWriterTest.java b/symmetric-io/src/test/java/org/jumpmind/symmetric/io/data/writer/TransformWriterTest.java
index 1af9c82..7ae7cef 100644
--- a/symmetric-io/src/test/java/org/jumpmind/symmetric/io/data/writer/TransformWriterTest.java
+++ b/symmetric-io/src/test/java/org/jumpmind/symmetric/io/data/writer/TransformWriterTest.java
@@ -28,28 +28,8 @@ import org.jumpmind.db.DbTestUtils;
 import org.jumpmind.db.model.Column;
 import org.jumpmind.db.model.Table;
 import org.jumpmind.symmetric.io.AbstractWriterTest;
-import org.jumpmind.symmetric.io.data.CsvData;
-import org.jumpmind.symmetric.io.data.DataEventType;
-import org.jumpmind.symmetric.io.data.transform.AdditiveColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.BinaryLeftColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.ClarionDateTimeColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.ColumnsToRowsKeyColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.ColumnsToRowsValueColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.ConstantColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.CopyColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.CopyIfChangedColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.IColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.IdentityColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.JavaColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.LeftColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.MathColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.MultiplierColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.RemoveColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.SubstrColumnTransform;
-import org.jumpmind.symmetric.io.data.transform.TransformColumn;
-import org.jumpmind.symmetric.io.data.transform.TransformPoint;
-import org.jumpmind.symmetric.io.data.transform.TransformTable;
-import org.jumpmind.symmetric.io.data.transform.ValueMapColumnTransform;
+import org.jumpmind.symmetric.io.data.*;
+import org.jumpmind.symmetric.io.data.transform.*;
 import org.junit.Assert;
 import org.junit.BeforeClass;
 import org.junit.Test;
@@ -81,8 +61,8 @@ public class TransformWriterTest extends AbstractWriterTest {
     public void testTableNameChange() {
         mockWriter.reset();
         Table table = new Table("s1", new Column("id"));
-        writeData(getTransformWriter(), new TableCsvData(table, new CsvData(DataEventType.INSERT,
-                new String[] { "66" }), new CsvData(DataEventType.INSERT, new String[] { "77" })));
+        writeData(getTransformWriter(), new TableCsvData(table, new CsvData(DataEventType.INSERT, new String[]{"66"}),
+           new CsvData(DataEventType.INSERT, new String[]{"77"})));
         List<CsvData> datas = mockWriter.writtenDatas.get(table.getFullyQualifiedTableName());
         Assert.assertNull(datas);
         datas = mockWriter.writtenDatas.get("t1");
@@ -95,8 +75,8 @@ public class TransformWriterTest extends AbstractWriterTest {
     public void testAddColumn() {
         mockWriter.reset();
         Table table = new Table("s2", new Column("id"));
-        writeData(getTransformWriter(), new TableCsvData(table, new CsvData(DataEventType.INSERT,
-                new String[] { "2" }), new CsvData(DataEventType.INSERT, new String[] { "1" })));
+        writeData(getTransformWriter(), new TableCsvData(table, new CsvData(DataEventType.INSERT, new String[]{"2"}),
+           new CsvData(DataEventType.INSERT, new String[]{"1"})));
         List<CsvData> datas = mockWriter.writtenDatas.get(table.getFullyQualifiedTableName());
         Assert.assertNull(datas);
         datas = mockWriter.writtenDatas.get("t2");
@@ -109,6 +89,25 @@ public class TransformWriterTest extends AbstractWriterTest {
     }
 
     @Test
+    public void testUpdateActionBeanShellScript() throws Exception {
+        mockWriter.reset();
+        Table table = new Table("s3", new Column("id"));
+        writeData(getTransformWriter(), new TableCsvData(table,
+           new CsvData(DataEventType.UPDATE, new String[]{"1"}),
+           new CsvData(DataEventType.UPDATE, new String[]{"2"}),
+           new CsvData(DataEventType.UPDATE, new String[]{"3"}),
+           new CsvData(DataEventType.UPDATE, new String[]{"4"}),
+           new CsvData(DataEventType.UPDATE, new String[]{"5"})));
+        List<CsvData> datas = mockWriter.writtenDatas.get("t3");
+        Assert.assertEquals(datas.size(), 4);
+        Assert.assertEquals(datas.get(0).getDataEventType(), DataEventType.INSERT);
+        Assert.assertEquals(datas.get(1).getDataEventType(), DataEventType.DELETE);
+        Assert.assertEquals(datas.get(2).getDataEventType(), DataEventType.UPDATE);
+        Assert.assertEquals(datas.get(3).getDataEventType(), DataEventType.UPDATE);
+    }
+
+
+    @Test
     public void testSimpleTableBeanShellMapping() throws Exception {
     }
 
@@ -125,11 +124,17 @@ public class TransformWriterTest extends AbstractWriterTest {
     }
 
     protected TransformWriter getTransformWriter() {
+        TransformTable transformTable3 =
+           new TransformTable("s3", "t3", TransformPoint.LOAD, new TransformColumn("id", "id", true));
+        transformTable3.setUpdateActionBeanScript("switch (id) { case \"1\": return \"INS_ROW\"; case \"2\": "
+           + "return \"DEL_ROW\"; case \"3\": return \"UPD_ROW\"; case \"4\": return \"NONE\"; case \"5\": "
+           + "return \"UPDATE_COL\"; }");
         return new TransformWriter(platform, TransformPoint.LOAD, mockWriter, buildDefaultColumnTransforms(), new TransformTable[] {
-                new TransformTable("s1", "t1", TransformPoint.LOAD, new TransformColumn("id", "id",
-                        true)),
-                new TransformTable("s2", "t2", TransformPoint.LOAD, new TransformColumn("id", "id",
-                        true), new TransformColumn(null, "col2", false, "const", "added")) });
+                new TransformTable("s1", "t1", TransformPoint.LOAD, new TransformColumn("id", "id", true)),
+                new TransformTable("s2", "t2", TransformPoint.LOAD, new TransformColumn("id", "id", true),
+                   new TransformColumn(null, "col2", false, "const", "added")),
+                transformTable3
+        });
     }
     
     public static Map<String, IColumnTransform<?>> buildDefaultColumnTransforms() {
-- 
1.7.10.4

msc

2015-08-10 12:20

reporter   ~0000708

Last edited: 2015-08-25 09:22

View 2 revisions

at github

https://github.com/NiasSt90/symmetric-ds/commit/f5d05253da54ccf6c4367fe296b0fa8ba57a7d11

msc

2015-08-12 08:04

reporter  

0002-Support-for-TableTransform-UpdateAction-with-Bean-Sh.patch (3,857 bytes)   
From 1ee098694f50dca5bce5756b116f3a1cb4d1d4c9 Mon Sep 17 00:00:00 2001
From: Markus Schulz <msc@onesty-tech.de>
Date: Wed, 12 Aug 2015 09:53:41 +0200
Subject: [PATCH] Support for TableTransform UpdateAction with
 Bean-Shell-Scripts: add the data from EXTERNAL_SELECT of a
 trigger to the bsh-interpreter as "externalData" variable.
 TODO: triggerId would be nice too...

---
 .../symmetric/io/data/transform/TransformTable.java        |   12 +++++++++++-
 .../symmetric/io/data/writer/TransformWriterTest.java      |   10 +++++-----
 2 files changed, 16 insertions(+), 6 deletions(-)

diff --git a/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TransformTable.java b/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TransformTable.java
index d4432a2..a7ec534 100644
--- a/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TransformTable.java
+++ b/symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TransformTable.java
@@ -25,7 +25,7 @@ import java.util.*;
 import bsh.Interpreter;
 import org.apache.commons.lang.StringUtils;
 import org.jumpmind.db.model.Table;
-import org.jumpmind.symmetric.io.data.DataContext;
+import org.jumpmind.symmetric.io.data.*;
 import org.jumpmind.symmetric.io.data.transform.TransformColumn.IncludeOnType;
 import org.jumpmind.util.Context;
 import org.slf4j.*;
@@ -228,6 +228,16 @@ public class TransformTable implements Cloneable {
             Map<String, String> sourceValues = transformedData.getSourceValues();
 
             try {
+                interpreter.set("sourceDmlType", transformedData.getSourceDmlType());
+                interpreter.set("sourceDmlTypeString", transformedData.getSourceDmlType().toString());
+                interpreter.set("transformedData", transformedData);
+                CsvData csvData = dataContext.getData();
+                if (csvData != null) {
+                    interpreter.set("externalData", csvData.getAttribute("externalData"));
+                }
+                else {
+                    interpreter.set("externalData", null);
+                }
                 for (String columnName : sourceValues.keySet()) {
                     interpreter.set(columnName.toUpperCase(), sourceValues.get(columnName));
                     interpreter.set(columnName, sourceValues.get(columnName));
diff --git a/symmetric-io/src/test/java/org/jumpmind/symmetric/io/data/writer/TransformWriterTest.java b/symmetric-io/src/test/java/org/jumpmind/symmetric/io/data/writer/TransformWriterTest.java
index 7ae7cef..2b06162 100644
--- a/symmetric-io/src/test/java/org/jumpmind/symmetric/io/data/writer/TransformWriterTest.java
+++ b/symmetric-io/src/test/java/org/jumpmind/symmetric/io/data/writer/TransformWriterTest.java
@@ -99,11 +99,11 @@ public class TransformWriterTest extends AbstractWriterTest {
            new CsvData(DataEventType.UPDATE, new String[]{"4"}),
            new CsvData(DataEventType.UPDATE, new String[]{"5"})));
         List<CsvData> datas = mockWriter.writtenDatas.get("t3");
-        Assert.assertEquals(datas.size(), 4);
-        Assert.assertEquals(datas.get(0).getDataEventType(), DataEventType.INSERT);
-        Assert.assertEquals(datas.get(1).getDataEventType(), DataEventType.DELETE);
-        Assert.assertEquals(datas.get(2).getDataEventType(), DataEventType.UPDATE);
-        Assert.assertEquals(datas.get(3).getDataEventType(), DataEventType.UPDATE);
+        Assert.assertEquals(4, datas.size());
+        Assert.assertEquals(DataEventType.INSERT, datas.get(0).getDataEventType());
+        Assert.assertEquals(DataEventType.DELETE, datas.get(1).getDataEventType());
+        Assert.assertEquals(DataEventType.UPDATE, datas.get(2).getDataEventType());
+        Assert.assertEquals(DataEventType.UPDATE, datas.get(3).getDataEventType());
     }
 
 
-- 
1.7.10.4

msc

2015-08-12 08:05

reporter   ~0000710

added some additional variables into the interpreter:
- sourceDmlType
- sourceDmlTypeString
- transformedData
- externalData (result from EXTERNAL_SELECT of the trigger)

msc

2015-08-25 09:23

reporter   ~0000714

Please use the github-url instead of the attached patch files, it contains the latest version of this patch.

chenson

2015-08-25 12:10

administrator   ~0000715

Can you submit a CLA? http://www.symmetricds.org/developer/contributor

msc

2015-08-28 07:19

reporter   ~0000716

done.

Related Changesets

SymmetricDS: 3.7 f5d05253

2015-08-10 06:50:29

msc

Details Diff
0002367: TableTransformation operation change support for INSERT/UPDATE-DML (in addition to DELETE)
allow access to COLUMN/OLD_COLUMN values in bsh-script
add the data from EXTERNAL_SELECT of a trigger to the bsh-interpreter as "externalData" variable.
allow evaluation of bsh-script for INSERT *and* UPDATE dml action
TODO: triggerId would be nice too...
TODO: merge deleteAction with updateAction to one single column called "targetDmlAction" where you can use static values or a bsh-script like in updateAction currently.
Affected Issues
0002367
mod - symmetric-assemble/src/asciidoc/configuration/transforms.ad Diff File
mod - symmetric-assemble/src/asciidoc/configuration/transforms/operation-change.ad Diff File
mod - symmetric-assemble/src/asciidoc/configuration/transforms/virtual-columns.ad Diff File
mod - symmetric-assemble/src/docbook/configuration.xml Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TransformService.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TransformServiceSqlMap.java Diff File
mod - symmetric-core/src/main/resources/symmetric-schema.xml Diff File
mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TransformTable.java Diff File
mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/TransformWriter.java Diff File
mod - symmetric-io/src/test/java/org/jumpmind/symmetric/io/data/writer/TransformWriterTest.java Diff File

SymmetricDS: 3.7 31eafad3

2015-09-24 15:21:13

chenson

Details Diff
0002367: TableTransformation operation change support for UPDATE-DML (in
addition to DELETE)
Affected Issues
0002367
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TransformService.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TransformServiceSqlMap.java Diff File
mod - symmetric-core/src/main/resources/symmetric-schema.xml Diff File
mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/transform/TransformTable.java Diff File
mod - symmetric-io/src/test/java/org/jumpmind/symmetric/io/data/writer/TransformWriterTest.java Diff File

SymmetricDS: 3.7 c9a0ff54

2015-12-09 21:31:09

chenson

Details Diff
0002467: Issue 0002367 broke insert transformations Affected Issues
0002367, 0002467
mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/TransformWriter.java Diff File

Issue History

Date Modified Username Field Change
2015-08-10 11:10 msc New Issue
2015-08-10 11:10 msc File Added: 0001-Support-for-TableTransform-UpdateAction-with-Bean-Sh.patch
2015-08-10 12:20 msc Note Added: 0000708
2015-08-12 08:04 msc File Added: 0002-Support-for-TableTransform-UpdateAction-with-Bean-Sh.patch
2015-08-12 08:05 msc Note Added: 0000710
2015-08-25 09:22 msc Note Edited: 0000708 View Revisions
2015-08-25 09:23 msc Note Added: 0000714
2015-08-25 12:10 chenson Note Added: 0000715
2015-08-28 07:19 msc Note Added: 0000716
2015-09-11 20:13 chenson Target Version => 3.7.22
2015-09-24 15:34 chenson Fixed in Version => 3.7.22
2015-09-24 19:00 msc Changeset attached => SymmetricDS 3.7 f5d05253
2015-09-24 19:21 chenson Status new => resolved
2015-09-24 19:21 chenson Resolution open => fixed
2015-09-24 19:21 chenson Assigned To => chenson
2015-09-24 20:00 chenson Changeset attached => SymmetricDS 3.7 31eafad3
2015-10-02 14:23 chenson Status resolved => closed
2015-12-10 03:00 chenson Changeset attached => SymmetricDS 3.7 c9a0ff54