View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0002367 | SymmetricDS | New Feature | public | 2015-08-10 11:10 | 2015-12-10 03:00 |
Reporter | msc | Assigned To | chenson | ||
Priority | normal | ||||
Status | closed | Resolution | fixed | ||
Product Version | 3.7.20 | ||||
Target Version | 3.7.22 | Fixed in Version | 3.7.22 | ||
Summary | 0002367: TableTransformation operation change support for UPDATE-DML (in addition to DELETE) | ||||
Description | currently 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. | ||||
Tags | No tags attached. | ||||
|
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 |
|
at github https://github.com/NiasSt90/symmetric-ds/commit/f5d05253da54ccf6c4367fe296b0fa8ba57a7d11 |
|
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 |
|
added some additional variables into the interpreter: - sourceDmlType - sourceDmlTypeString - transformedData - externalData (result from EXTERNAL_SELECT of the trigger) |
|
Please use the github-url instead of the attached patch files, it contains the latest version of this patch. |
|
Can you submit a CLA? http://www.symmetricds.org/developer/contributor |
|
done. |
SymmetricDS: 3.7 f5d05253 2015-08-10 06:50:29 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 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 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 |
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 |